SELECT
t20.usr_id,
COUNT(DISTINCT t3.v_typ) AS type_count,
COUNT(*) AS total_views,
ROUND(COUNT(DISTINCT t3.v_typ) * 100.0 / COUNT(*), 2) AS diversity_index,
DENSE_RANK() OVER (ORDER BY COUNT(DISTINCT t3.v_typ) DESC) AS diversity_rank
FROM bilibili_t20 t20
JOIN bilibili_t3 t3 ON t20.v_id = t3.v_id
GROUP BY t20.usr_id
ORDER BY diversity_rank
LIMIT 3
WITH daily_watch AS (
SELECT DISTINCT usr_id, DATE(v_tm) AS watch_date
FROM bilibili_t20
),
streak_groups AS (
SELECT
usr_id,
watch_date,
watch_date - INTERVAL ROW_NUMBER() OVER (PARTITION BY usr_id ORDER BY watch_date) DAY AS grp
FROM daily_watch
),
streak_counts AS (
SELECT usr_id, grp, COUNT(*) AS streak
FROM streak_groups
GROUP BY usr_id, grp
)
SELECT
usr_id,
MAX(streak) AS max_streak
FROM streak_counts
GROUP BY usr_id
HAVING max_streak >= 5
ORDER BY max_streak DESC
LIMIT 3
SELECT
e.name,
a.timestamp,
CASE
WHEN HOUR(a.timestamp) < 6 THEN '凌晨异常'
WHEN HOUR(a.timestamp) > 22 THEN '深夜异常'
ELSE '正常时段'
END AS time_type,
a.access_type
FROM employees e
JOIN access_control a ON e.id = a.employee_id
WHERE HOUR(a.timestamp) < 6 OR HOUR(a.timestamp) > 22
ORDER BY a.timestamp DESC
LIMIT 3
with t as(
select distinct employee_id,date(punch_time) as pt
from attendance
),
t1 as (
select employee_id,pt,row_number()over(partition by employee_id order by pt) as gp
from t
),
t2 as (
select employee_id,date_sub(pt, interval gp day) as grp
from t1
),
t3 as (
select employee_id,count(*) as conse_days
from t2
group by employee_id,grp
)
select employee_id,max(conse_days) as d
from t3
group by employee_id
order by d desc
limit 3
with t as(
select distinct employee_id,date(punch_time) as pt
from attendance
),
t1 as (
select employee_id,pt,row_number()over(partition by employee_id order by pt) as gp
from t
),
t2 as (
select employee_id,date_sub(pt, interval gp day) as grp
from t1
),
t3 as (
select employee_id,count(*) as conse_days
from t2
group by employee_id,grp
)
select employee_id,max(conse_days) as d
from t3
group by employee_id
order by d desc
with ride_days as
(
select distinct user_id,date(start_time) as ride_date
from hello_bike_riding_rcd
),
gp as (
select
user_id,
ride_date,
date_sub(ride_date, interval rn day) as grp
from (
select
user_id,
ride_date,
row_number()over(partition by user_id order by ride_date) as rn
from ride_days
) t
),
conse as (
select user_id,grp,count(1) as conse_days
from gp
group by user_id,grp
)
select user_id,conse_days
from conse
order by conse_days desc
limit 3
with ride_days as
(
select distinct user_id,date(start_time) as ride_date
from hello_bike_riding_rcd
),
gp as (
select
user_id,
ride_date,
date_sub(ride_date, interval rn day) as grp
from (
select
user_id,
ride_date,
row_number()over(partition by user_id order by ride_date) as rn
from ride_days
) t
),
conse as (
select user_id,grp,count(1) as conse_days
from gp
group by user_id,grp
)
select user_id,conse_days
from conse
order by conse_days desc
limit 10
WITH RecentOrder AS (
SELECT cust_uid, DATEDIFF(CURRENT_DATE(), MAX(trx_dt)) AS recency_days
FROM mt_trx_rcd_f
GROUP BY cust_uid
),
ActiveDaysFrequency AS (
SELECT cust_uid, COUNT(DISTINCT DATE(trx_dt)) AS frequency_days
FROM mt_trx_rcd_f
GROUP BY cust_uid
),
AverageSpending AS (
SELECT cust_uid, AVG(trx_amt) AS avg_monetary
FROM mt_trx_rcd_f
GROUP BY cust_uid
),
FirstTransaction AS (
SELECT
cust_uid AS user_id,
DATE_FORMAT(MIN(trx_dt), '%Y-%m') AS first_trx_month
FROM mt_trx_rcd_f
GROUP BY cust_uid
),
CalculateRecencyScores AS (
SELECT
cust_uid AS user_id,
NTILE(3) OVER (ORDER BY recency_days DESC) AS recency_score
FROM RecentOrder
),
CalculateFrequencyScores AS (
SELECT
cust_uid AS user_id,
NTILE(3) OVER (ORDER BY frequency_days) AS frequency_score
FROM ActiveDaysFrequency
),
CalculateMonetaryScores AS (
SELECT
cust_uid AS user_id,
NTILE(3) OVER (ORDER BY avg_monetary DESC) AS monetary_score
FROM AverageSpending
)
SELECT
crs.user_id,
crs.recency_score,
cfs.frequency_score,
cms.monetary_score,
ft.first_trx_month
FROM CalculateRecencyScores crs
JOIN CalculateFrequencyScores cfs ON crs.user_id = cfs.user_id
JOIN CalculateMonetaryScores cms ON crs.user_id = cms.user_id
JOIN FirstTransaction ft ON crs.user_id = ft.user_id
order by crs.user_id
with basic
as (select
cust_uid,
datediff(current_date,max(trx_dt))as rec,
count(distinct trx_dt) as fre,
avg(trx_amt) as mon,
date_format(min(trx_dt),'%Y-%m') as fir
from mt_trx_rcd_f
group by cust_uid)
select
cust_uid,
ntile(3) over(order by rec) as rec_sc,ntile(3) over(order by fre) as fre_sc,
ntile(3) over(order by mon) as mon_sc,
fir
from basic
with basic
as (select
cust_uid,
datediff(current_date,max(trx_dt))as rec,
count(distinct trx_dt) as fre,
avg(trx_amt) as mon,
date_format(min(trx_dt),'%Y-%m') as fir
from mt_trx_rcd_f
group by cust_uid)
select
cust_uid,
ntile(3) over(order by rec) as rec_sc,
ntile(3) over(order by fre) as fre_sc,
ntile(3) over(order by mon) as mon_sc,
fir
from basic
SELECT employee_id, DATE(punch_time) AS punch_date
FROM attendance
WHERE TIME(punch_time) BETWEEN "00:00:00" AND "05:00:00"
GROUP BY employee_id, DATE(punch_time)
with ee as
(select em.id,
date(em.hire_date) as hire_date,
min(punch_time) as et
from employees as em
join attendance as at
on em.id = at.employee_id
group by em.id,date(em.hire_date)
),
rn as
(select id,hire_date,row_number()over(partition by id order by hire_date) as rnk
from ee
where hour(et) between 0 and 4),
grp
as
(
select id,hire_date,date_sub(hire_date,interval rnk day) as grpp
from rn
)
select id ,count(*) as consecutive_days
from grp
group by id,grpp
having count(*)>=3
WITH first_ride AS (
SELECT user_id, MIN(DATE(start_time)) AS first_date
FROM hello_bike_riding_rcd
GROUP BY user_id
),
daily_users AS (
SELECT DISTINCT user_id, DATE(start_time) AS ride_date
FROM hello_bike_riding_rcd
)
SELECT
f.first_date,
COUNT(DISTINCT f.user_id) AS new_users,
COUNT(DISTINCT d.user_id) AS retained_users,
ROUND(COUNT(DISTINCT d.user_id) / COUNT(DISTINCT f.user_id) * 100, 2) AS retention_rate
FROM first_ride f
LEFT JOIN daily_users d ON f.user_id = d.user_id
AND d.ride_date = DATE_ADD(f.first_date, INTERVAL 7 DAY)
WHERE f.first_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY f.first_date
ORDER BY f.first_date
LIMIT 3
WITH first_ride AS (
SELECT user_id, MIN(DATE(start_time)) AS first_date
FROM hello_bike_riding_rcd
GROUP BY user_id
),
daily_users AS (
SELECT DISTINCT user_id, DATE(start_time) AS ride_date
FROM hello_bike_riding_rcd
)
SELECT
f.first_date,
COUNT(DISTINCT f.user_id) AS new_users,
COUNT(DISTINCT d.user_id) AS retained_users,
ROUND(COUNT(DISTINCT d.user_id) / COUNT(DISTINCT f.user_id) * 100, 2) AS retention_rate
FROM first_ride f
LEFT JOIN daily_users d ON f.user_id = d.user_id
AND d.ride_date = DATE_ADD(f.first_date, INTERVAL 7 DAY)
WHERE f.first_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY f.first_date
ORDER BY f.first_date
limit 3
WITH first_ride AS (
SELECT user_id, MIN(DATE(start_time)) AS first_date
FROM hello_bike_riding_rcd
GROUP BY user_id
),
daily_users AS (
SELECT DISTINCT user_id, DATE(start_time) AS ride_date
FROM hello_bike_riding_rcd
)
SELECT
f.first_date,
COUNT(DISTINCT f.user_id) AS new_users,
COUNT(DISTINCT d.user_id) AS retained_users,
ROUND(COUNT(DISTINCT d.user_id) / COUNT(DISTINCT f.user_id) * 100, 2) AS retention_rate
FROM first_ride f
LEFT JOIN daily_users d ON f.user_id = d.user_id
AND d.ride_date = DATE_ADD(f.first_date, INTERVAL 7 DAY)
WHERE f.first_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY f.first_date
ORDER BY f.first_date
WITH first_watch AS (
SELECT usr_id, MIN(DATE(v_tm)) AS first_date
FROM bilibili_t20
GROUP BY usr_id
),
daily_watch AS (
SELECT DISTINCT usr_id, DATE(v_tm) AS watch_date
FROM bilibili_t20
)
SELECT
f.first_date,
COUNT(DISTINCT f.usr_id) AS new_users,
COUNT(DISTINCT d.usr_id) AS retained_users,
ROUND(COUNT(DISTINCT d.usr_id) / COUNT(DISTINCT f.usr_id) * 100, 2) AS retention_rate
FROM first_watch f
LEFT JOIN daily_watch d ON f.usr_id = d.usr_id
AND d.watch_date = DATE_ADD(f.first_date, INTERVAL 30 DAY)
WHERE f.first_date BETWEEN '2024-01-01' AND '2024-01-31'
GROUP BY f.first_date
ORDER BY f.first_date
LIMIT 3
SELECT
distinct
u.usr_id,
h.hotel_cnt,
c.car_cnt
FROM
cmb_usr_trx_rcd as u
join (
SELECT usr_id, COUNT(*) as hotel_cnt
FROM cmb_usr_trx_rcd
WHERE mch_nm LIKE '%酒店%'
GROUP BY usr_id
) h
on u.usr_id = h.usr_id
JOIN (
SELECT usr_id, COUNT(*) as car_cnt
FROM cmb_usr_trx_rcd
WHERE mch_nm LIKE '%加油%' OR mch_nm LIKE '%洗车%'
GROUP BY usr_id
) c ON u.usr_id = c.usr_id
where h.hotel_cnt>=1 and c.car_cnt>=1
ORDER BY h.hotel_cnt + c.car_cnt DESC;
WITH tag AS (
SELECT
*,
CASE WHEN mch_nm LIKE '%酒店%' THEN 1 ELSE 0 END AS hoteltag,
CASE WHEN mch_nm LIKE '%加油%' OR mch_nm LIKE '%洗车%' THEN 1 ELSE 0 END AS cartag
FROM cmb_usr_trx_rcd
)
SELECT
usr_id,
SUM(hoteltag) AS hotel_cnt,
SUM(cartag) AS car_cnt
FROM tag
GROUP BY usr_id
HAVING SUM(hoteltag) >= 1
AND SUM(cartag) >= 1;
SELECT
h.usr_id,
h.hotel_cnt,
c.car_cnt
FROM (
SELECT usr_id, COUNT(*) as hotel_cnt
FROM cmb_usr_trx_rcd
WHERE mch_nm LIKE '%酒店%'
GROUP BY usr_id
) h
JOIN (
SELECT usr_id, COUNT(*) as car_cnt
FROM cmb_usr_trx_rcd
WHERE mch_nm LIKE '%加油%' OR mch_nm LIKE '%洗车%'
GROUP BY usr_id
) c ON h.usr_id = c.usr_id
ORDER BY h.hotel_cnt + c.car_cnt DESC;
with tag as (select *,case when mch_nm like '%酒店%' then 1 end as hoteltag,
case when mch_nm like '加油'or mch_nm like "洗车" then 1 end as cartag
from cmb_usr_trx_rcd)
select usr_id,sum(hoteltag) as hotel_cnt,
sum(cartag) as car_cnt
from tag
group by usr_id
having sum(hoteltag)>=1 and sum(cartag)>=1