排名

用户解题统计

过去一年提交了

勋章 ①金银铜：在竞赛中获得第一二三名；②好习惯：自然月10天提交；③里程碑：解决1/2/5/10/20/50/100/200题；④每周打卡挑战：完成每周5题，每年1月1日清零。

收藏

收藏日期 题目名称 解决状态
2024-12-06 抖音面试真题（1）T+1日留存率  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
没有评论过的题目。

提交记录

提交日期 题目名称 提交代码
2025-02-11 抖音面试真题（4）T+1月留存  
WITH monthly_unique_logins AS (
    SELECT
        usr_id,
        DATE_FORMAT(login_time, '%Y-%m-01') AS login_month
    FROM
        user_login_log
    WHERE
        login_time >= '2024-01-01' AND login_time < '2025-01-01'
    GROUP BY
        usr_id,
        DATE_FORMAT(login_time, '%Y-%m-01')
),
next_month_logins AS (
    SELECT
        mul1.usr_id,
        mul1.login_month AS current_month,
        mul2.login_month AS next_month
    FROM
        monthly_unique_logins mul1
    LEFT JOIN
        monthly_unique_logins mul2
    ON
        mul1.usr_id = mul2.usr_id AND
        mul2.login_month = DATE_ADD(mul1.login_month, INTERVAL 1 MONTH)
)
SELECT
    current_month,
    ROUND(COUNT(DISTINCT CASE WHEN next_month IS NOT NULL THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_1_month_retention_rate
FROM
    next_month_logins
WHERE
    current_month >= '2024-01-01' AND current_month < '2024-12-01'
GROUP BY
    current_month
ORDER BY
    current_month;
2025-02-11 抖音面试真题（4）T+1月留存  
WITH monthly_unique_logins AS (
SELECT
usr_id,
DATE_FORMAT(login_time, '%Y-%m-01') AS login_month
FROM
user_login_log
WHERE
login_time >= '2024-01-01' AND login_time < '2025-01-01'
GROUP BY
usr_id, DATE_FORMAT(login_time, '%Y-%m-01')
),
next_month_logins AS (
SELECT
mul1.usr_id,
mul1.login_month AS current_month,
mul2.login_month AS next_month
FROM
monthly_unique_logins mul1
LEFT JOIN monthly_unique_logins mul2 ON mul1.usr_id = mul2.usr_id
AND mul2.login_month = DATE_FORMAT(DATE_ADD(mul1.login_month, INTERVAL 1 MONTH), '%Y-%m-01')
)
SELECT
current_month,
ROUND(COUNT(DISTINCT CASE WHEN next_month IS NOT NULL THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_1_month_retention_rate
FROM
next_month_logins
GROUP BY
current_month
ORDER BY
current_month;
2025-02-11 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  
WITH data1 AS (
SELECT DISTINCT
usr_id,
DATE(login_time) AS login_date
FROM
user_login_log
WHERE
DATEDIFF(CURRENT_DATE, DATE(login_time)) <= 90
),
data_t3_retention AS (
SELECT
T.login_date AS base_date,
COUNT(DISTINCT T_plus.usr_id) AS retained_users_t3
FROM
data1 T
LEFT JOIN data1 T_plus ON T.usr_id = T_plus.usr_id
AND T_plus.login_date BETWEEN DATE_ADD(T.login_date, INTERVAL 2 DAY) 
 AND DATE_ADD(T.login_date, INTERVAL 3 DAY)
GROUP BY
T.login_date
),
data_t7_retention AS (
SELECT
T.login_date AS base_date,
COUNT(DISTINCT T_plus.usr_id) AS retained_users_t7
FROM
data1 T
LEFT JOIN data1 T_plus ON T.usr_id = T_plus.usr_id
AND T_plus.login_date BETWEEN DATE_ADD(T.login_date, INTERVAL 2 DAY) 
 AND DATE_ADD(T.login_date, INTERVAL 7 DAY)
GROUP BY
T.login_date
),
data_t14_retention AS (
SELECT
T.login_date AS base_date,
COUNT(DISTINCT T_plus.usr_id) AS retained_users_t14
FROM
data1 T
LEFT JOIN data1 T_plus ON T.usr_id = T_plus.usr_id
AND T_plus.login_date BETWEEN DATE_ADD(T.login_date, INTERVAL 2 DAY) 
 AND DATE_ADD(T.login_date, INTERVAL 14 DAY)
GROUP BY
T.login_date
)
SELECT
d1.login_date as first_login_date,
ROUND(Max(d3.retained_users_t3) * 100.0 / COUNT(DISTINCT d1.usr_id), 2) AS t_plus_3_retention_rate,
ROUND(MAX(d7.retained_users_t7) * 100.0 / COUNT(DISTINCT d1.usr_id), 2) AS t_plus_7_retention_rate,
ROUND(MAX(d14.retained_users_t14) * 100.0 / COUNT(DISTINCT d1.usr_id), 2) AS t_plus_14_retention_rate
FROM
data1 d1
LEFT JOIN data_t3_retention d3 ON d1.login_date = d3.base_date
LEFT JOIN data_t7_retention d7 ON d1.login_date = d7.base_date
LEFT JOIN data_t14_retention d14 ON d1.login_date = d14.base_date
GROUP BY
d1.login_date
ORDER BY
d1.login_date;
2025-02-11 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  
WITH data1 AS (
SELECT DISTINCT
usr_id,
DATE(login_time) AS login_date
FROM
user_login_log
WHERE
DATEDIFF(CURRENT_DATE, DATE(login_time)) <= 90
),
data_t3_retention AS (
SELECT
T.login_date AS base_date,
COUNT(DISTINCT T_plus.usr_id) AS retained_users_t3
FROM
data1 T
LEFT JOIN data1 T_plus ON T.usr_id = T_plus.usr_id
AND T_plus.login_date BETWEEN DATE_ADD(T.login_date, INTERVAL 1 DAY) 
 AND DATE_ADD(T.login_date, INTERVAL 3 DAY)
GROUP BY
T.login_date
),
data_t7_retention AS (
SELECT
T.login_date AS base_date,
COUNT(DISTINCT T_plus.usr_id) AS retained_users_t7
FROM
data1 T
LEFT JOIN data1 T_plus ON T.usr_id = T_plus.usr_id
AND T_plus.login_date BETWEEN DATE_ADD(T.login_date, INTERVAL 1 DAY) 
 AND DATE_ADD(T.login_date, INTERVAL 7 DAY)
GROUP BY
T.login_date
),
data_t14_retention AS (
SELECT
T.login_date AS base_date,
COUNT(DISTINCT T_plus.usr_id) AS retained_users_t14
FROM
data1 T
LEFT JOIN data1 T_plus ON T.usr_id = T_plus.usr_id
AND T_plus.login_date BETWEEN DATE_ADD(T.login_date, INTERVAL 1 DAY) 
 AND DATE_ADD(T.login_date, INTERVAL 14 DAY)
GROUP BY
T.login_date
)
SELECT
d1.login_date as first_login_date,
ROUND(Max(d3.retained_users_t3) * 100.0 / COUNT(DISTINCT d1.usr_id), 2) AS t_plus_3_retention_rate,
ROUND(MAX(d7.retained_users_t7) * 100.0 / COUNT(DISTINCT d1.usr_id), 2) AS t_plus_7_retention_rate,
ROUND(MAX(d14.retained_users_t14) * 100.0 / COUNT(DISTINCT d1.usr_id), 2) AS t_plus_14_retention_rate
FROM
data1 d1
LEFT JOIN data_t3_retention d3 ON d1.login_date = d3.base_date
LEFT JOIN data_t7_retention d7 ON d1.login_date = d7.base_date
LEFT JOIN data_t14_retention d14 ON d1.login_date = d14.base_date
GROUP BY
d1.login_date
ORDER BY
d1.login_date;
2025-02-11 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  
WITH data1 AS (
SELECT DISTINCT
usr_id,
DATE(login_time) AS login_date
FROM
user_login_log
WHERE
DATEDIFF(CURRENT_DATE, DATE(login_time)) <= 90
),
data_t3_retention AS (
SELECT
T.login_date AS base_date,
COUNT(DISTINCT T_plus.usr_id) AS retained_users_t3
FROM
data1 T
LEFT JOIN data1 T_plus ON T.usr_id = T_plus.usr_id
AND T_plus.login_date BETWEEN DATE_ADD(T.login_date, INTERVAL 1 DAY) 
 AND DATE_ADD(T.login_date, INTERVAL 3 DAY)
GROUP BY
T.login_date
),
data_t7_retention AS (
SELECT
T.login_date AS base_date,
COUNT(DISTINCT T_plus.usr_id) AS retained_users_t7
FROM
data1 T
LEFT JOIN data1 T_plus ON T.usr_id = T_plus.usr_id
AND T_plus.login_date BETWEEN DATE_ADD(T.login_date, INTERVAL 1 DAY) 
 AND DATE_ADD(T.login_date, INTERVAL 7 DAY)
GROUP BY
T.login_date
),
data_t14_retention AS (
SELECT
T.login_date AS base_date,
COUNT(DISTINCT T_plus.usr_id) AS retained_users_t14
FROM
data1 T
LEFT JOIN data1 T_plus ON T.usr_id = T_plus.usr_id
AND T_plus.login_date BETWEEN DATE_ADD(T.login_date, INTERVAL 1 DAY) 
 AND DATE_ADD(T.login_date, INTERVAL 14 DAY)
GROUP BY
T.login_date
)
SELECT
d1.login_date,
ROUND(Max(d3.retained_users_t3) * 100.0 / COUNT(DISTINCT d1.usr_id), 2) AS T3_retention_rate,
ROUND(MAX(d7.retained_users_t7) * 100.0 / COUNT(DISTINCT d1.usr_id), 2) AS T7_retention_rate,
ROUND(MAX(d14.retained_users_t14) * 100.0 / COUNT(DISTINCT d1.usr_id), 2) AS T14_retention_rate
FROM
data1 d1
LEFT JOIN data_t3_retention d3 ON d1.login_date = d3.base_date
LEFT JOIN data_t7_retention d7 ON d1.login_date = d7.base_date
LEFT JOIN data_t14_retention d14 ON d1.login_date = d14.base_date
GROUP BY
d1.login_date
ORDER BY
d1.login_date;
2025-02-11 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  
WITH data1 AS (
SELECT DISTINCT
usr_id,
DATE(login_time) AS login_date
FROM
user_login_log
WHERE
DATEDIFF(CURRENT_DATE, DATE(login_time)) <= 90
),
data_t1 AS (
SELECT
T.login_date AS base_date,
COUNT(DISTINCT T_1.usr_id) AS next_day_users
FROM
data1 T
LEFT JOIN data1 T_1 ON T.usr_id = T_1.usr_id
AND T_1.login_date = DATE_ADD(T.login_date, INTERVAL 1 DAY)
GROUP BY
T.login_date
),
data_t3 AS (
SELECT
T.login_date AS base_date,
COUNT(DISTINCT T_3.usr_id) AS day3_users
FROM
data1 T
LEFT JOIN data1 T_3 ON T.usr_id = T_3.usr_id
AND T_3.login_date = DATE_ADD(T.login_date, INTERVAL 3 DAY)
GROUP BY
T.login_date
),
data_t7 AS (
SELECT
T.login_date AS base_date,
COUNT(DISTINCT T_7.usr_id) AS day7_users
FROM
data1 T
LEFT JOIN data1 T_7 ON T.usr_id = T_7.usr_id
AND T_7.login_date = DATE_ADD(T.login_date, INTERVAL 7 DAY)
GROUP BY
T.login_date
),
data_t14 AS (
SELECT
T.login_date AS base_date,
COUNT(DISTINCT T_14.usr_id) AS day14_users
FROM
data1 T
LEFT JOIN data1 T_14 ON T.usr_id = T_14.usr_id
AND T_14.login_date = DATE_ADD(T.login_date, INTERVAL 14 DAY)
GROUP BY
T.login_date
)
SELECT
d1.login_date,
ROUND(MAX(d3.day3_users) * 100.0 / COUNT(DISTINCT d1.usr_id), 2) AS T3_retention_rate,
ROUND(MAX(d7.day7_users) * 100.0 / COUNT(DISTINCT d1.usr_id), 2) AS T7_retention_rate,
ROUND(MAX(d14.day14_users) * 100.0 / COUNT(DISTINCT d1.usr_id), 2) AS T14_retention_rate
FROM
data1 d1
LEFT JOIN data_t3 d3 ON d1.login_date = d3.base_date
LEFT JOIN data_t7 d7 ON d1.login_date = d7.base_date
LEFT JOIN data_t14 d14 ON d1.login_date = d14.base_date
GROUP BY
d1.login_date
ORDER BY
d1.login_date;
2025-02-11 抖音面试真题（1）T+1日留存率  
WITH d1 AS (
SELECT
DATE(login_time) AS login_date,
COUNT(distinct usr_id) AS count_user
FROM
user_login_log
WHERE
DATEDIFF(CURRENT_DATE, DATE(login_time)) <= 30
GROUP BY
DATE(login_time)
),
d2 AS (
SELECT
DATE(d1.login_time) AS base_date,
COUNT(distinct d2.usr_id) AS next_day_users
FROM
user_login_log d1
LEFT JOIN user_login_log d2 ON d1.usr_id = d2.usr_id
AND DATE(d2.login_time) = DATE_ADD(DATE(d1.login_time), INTERVAL 1 DAY)
WHERE
DATEDIFF(CURRENT_DATE, DATE(d1.login_time)) <= 30
GROUP BY
DATE(d1.login_time)
)
SELECT
d1.login_date,
CONCAT(ROUND(d2.next_day_users * 100.0 / d1.count_user, 2), '%') AS T1_retention_rate
FROM
d1
LEFT JOIN d2 ON d1.login_date = d2.base_date
ORDER BY
d1.login_date;
2025-02-11 抖音面试真题（1）T+1日留存率  
SELECT
DATE(a.login_time) AS login_date,
concat(ROUND(COUNT(b.usr_id) * 100.0 / COUNT(a.usr_id), 2),"%") AS retention_rate
FROM
user_login_log a
LEFT JOIN
user_login_log b ON a.usr_id = b.usr_id AND DATE(b.login_time) = DATE_ADD(DATE(a.login_time), INTERVAL 1 DAY)
WHERE
DATE(a.login_time) BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND DATE_SUB(CURDATE(), INTERVAL 0 DAY)
GROUP BY
DATE(a.login_time)
ORDER BY
DATE(a.login_time);
2025-02-11 抖音面试真题（1）T+1日留存率  
SELECT
DATE(a.login_time) AS login_date,
concat(ROUND(COUNT(b.usr_id) * 100.0 / COUNT(a.usr_id), 2),"%") AS retention_rate
FROM
user_login_log a
LEFT JOIN
user_login_log b ON a.usr_id = b.usr_id AND DATE(b.login_time) = DATE_ADD(DATE(a.login_time), INTERVAL 1 DAY)
WHERE
DATE(a.login_time) BETWEEN DATE_SUB(CURDATE(), INTERVAL 30 DAY) AND DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY
DATE(a.login_time)
ORDER BY
DATE(a.login_time);
2025-01-31 Halo出行-通勤活跃用户标签开发  
WITH corrected_gd_loc AS ( 
SELECT 
loc_nm,
CASE 
WHEN loc_nm = '北京机床研究所' THEN '写字楼'
ELSE loc_type 
END AS loc_type
FROM gd_loc_map
),
filtered_rides AS (
SELECT 
r.user_id,
DATE(r.start_time) AS ride_date,
DATE_FORMAT(r.start_time, '%Y-%m') AS ride_month
FROM hello_bike_riding_rcd r
INNER JOIN corrected_gd_loc start_loc 
ON r.start_loc = start_loc.loc_nm
INNER JOIN corrected_gd_loc end_loc
ON r.end_loc = end_loc.loc_nm
WHERE 
(
(start_loc.loc_type = '地铁站' AND end_loc.loc_type = '写字楼')
OR
(start_loc.loc_type = '写字楼' AND end_loc.loc_type = '地铁站')
)
AND r.start_time BETWEEN '2020-01-01' AND '2024-12-31'
),
daily_commutes AS (
SELECT 
user_id,
ride_month,
ride_date
FROM filtered_rides
GROUP BY user_id, ride_month, ride_date 
),
monthly_counts AS (
SELECT 
user_id,
ride_month,
COUNT(DISTINCT ride_date) AS num_days
FROM daily_commutes
GROUP BY user_id, ride_month
HAVING num_days >= 5
),
consecutive_months AS (
SELECT 
user_id,
ride_month,
LEAD(ride_month, 1) OVER (PARTITION BY user_id ORDER BY ride_month) AS next_month1,
LEAD(ride_month, 2) OVER (PARTITION BY user_id ORDER BY ride_month) AS next_month2
FROM monthly_counts
),
qualified_users AS (
SELECT DISTINCT user_id
FROM consecutive_months
WHERE 
next_month1 = DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(ride_month, '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH), '%Y-%m')
AND next_month2 = DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(ride_month, '-01'), '%Y-%m-%d'), INTERVAL 2 MONTH), '%Y-%m')
)
SELECT 
distinct u.user_id,
COALESCE(MAX(q.active_tag), 0) AS active_tag
FROM (SELECT DISTINCT user_id FROM hello_bike_riding_rcd) u
LEFT JOIN (
SELECT user_id, 1 AS active_tag 
FROM qualified_users
) q ON u.user_id = q.user_id
GROUP BY u.user_id;
2025-01-31 Halo出行-通勤活跃用户标签开发  
WITH filtered_rides AS (
SELECT 
r.user_id,
DATE(r.start_time) AS ride_date,
DATE_FORMAT(r.start_time, '%Y-%m') AS ride_month
FROM hello_bike_riding_rcd r
INNER JOIN gd_loc_map start_loc ON r.start_loc = start_loc.loc_nm
INNER JOIN gd_loc_map end_loc ON r.end_loc = end_loc.loc_nm
WHERE 
(
(start_loc.loc_type = '地铁站' AND end_loc.loc_type = '写字楼')
OR
(start_loc.loc_type = '写字楼' AND end_loc.loc_type = '地铁站')
)
AND r.start_time BETWEEN '2020-01-01' AND '2024-12-31'
),
daily_commutes AS (
SELECT 
user_id,
ride_month,
ride_date
FROM filtered_rides
GROUP BY user_id, ride_month, ride_date
),
monthly_counts AS (
SELECT 
user_id,
ride_month,
COUNT(DISTINCT ride_date) AS num_days
FROM daily_commutes
GROUP BY user_id, ride_month
HAVING num_days >= 5
),
consecutive_months AS (
SELECT 
user_id,
ride_month,
LEAD(ride_month, 1) OVER (PARTITION BY user_id ORDER BY ride_month) AS next_month1,
LEAD(ride_month, 2) OVER (PARTITION BY user_id ORDER BY ride_month) AS next_month2
FROM monthly_counts
),
qualified_users AS (
SELECT DISTINCT user_id
FROM consecutive_months
WHERE 
next_month1 = DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(ride_month, '-01'), '%Y-%m-%d'), INTERVAL 1 MONTH), '%Y-%m')
AND next_month2 = DATE_FORMAT(DATE_ADD(STR_TO_DATE(CONCAT(ride_month, '-01'), '%Y-%m-%d'), INTERVAL 2 MONTH), '%Y-%m')
)
SELECT 
distinct u.user_id,
CASE WHEN q.user_id IS NOT NULL THEN 1 ELSE 0 END AS active_tag
FROM (SELECT DISTINCT user_id FROM hello_bike_riding_rcd) u
LEFT JOIN qualified_users q ON u.user_id = q.user_id;
2025-01-31 Halo出行-通勤活跃用户标签开发  
WITH commute_rides AS (
SELECT
r.user_id,
DATE_FORMAT(r.start_time, '%Y-%m-01') AS ride_month,
DATE(r.start_time) AS ride_date
FROM hello_bike_riding_rcd r
JOIN gd_loc_map m1 ON r.start_loc = m1.loc_nm
JOIN gd_loc_map m2 ON r.end_loc = m2.loc_nm
WHERE
(
(
CASE WHEN m1.loc_nm = '北京机床研究所' THEN '写字楼' ELSE m1.loc_type END
= '地铁站'
AND
CASE WHEN m2.loc_nm = '北京机床研究所' THEN '写字楼' ELSE m2.loc_type END
= '写字楼'
)
OR
(
CASE WHEN m1.loc_nm = '北京机床研究所' THEN '写字楼' ELSE m1.loc_type END
= '写字楼'
AND
CASE WHEN m2.loc_nm = '北京机床研究所' THEN '写字楼' ELSE m2.loc_type END
= '地铁站'
)
)
AND r.start_time BETWEEN '2020-01-01' AND '2024-12-31'
),
monthly_ride_days AS (
SELECT
user_id,
ride_month,
COUNT(DISTINCT ride_date) AS commute_days
FROM commute_rides
GROUP BY user_id, ride_month
),
monthly_flags AS (
SELECT
user_id,
ride_month,
CASE WHEN commute_days >= 5 THEN 1 ELSE 0 END AS is_active
FROM monthly_ride_days
),
three_month_window AS (
SELECT
user_id,
ride_month,
is_active,
LAG(is_active, 1) OVER (PARTITION BY user_id ORDER BY ride_month) AS m_minus_1,
LAG(is_active, 2) OVER (PARTITION BY user_id ORDER BY ride_month) AS m_minus_2
FROM monthly_flags
)
SELECT
DISTINCT user_id,
CASE
WHEN MAX(
CASE
WHEN m_minus_2 = 1 AND m_minus_1 = 1 AND is_active = 1 THEN 1
ELSE 0
END
) OVER (PARTITION BY user_id) = 1
THEN 1
ELSE 0
END AS active_tag
FROM three_month_window
GROUP BY user_id, ride_month, is_active, m_minus_1, m_minus_2
ORDER BY user_id;
2025-01-31 Halo出行-通勤活跃用户标签开发  
WITH commute_rides AS (
SELECT
r.user_id,
DATE_FORMAT(r.start_time, '%Y-%m-01') AS ride_month,
DATE(r.start_time) AS ride_date
FROM hello_bike_riding_rcd r
JOIN gd_loc_map m1 ON r.start_loc = m1.loc_nm
JOIN gd_loc_map m2 ON r.end_loc = m2.loc_nm
WHERE (
(m1.loc_type = '地铁站' AND m2.loc_type = '写字楼')
OR (m1.loc_type = '写字楼' AND m2.loc_type = '地铁站')
)
AND r.start_time BETWEEN '2020-01-01' AND '2024-12-31'
),
monthly_ride_days AS (
SELECT
user_id,
ride_month,
COUNT(DISTINCT ride_date) AS commute_days
FROM commute_rides
GROUP BY user_id, ride_month
),
monthly_flags AS (
SELECT
user_id,
ride_month,
CASE WHEN commute_days >= 5 THEN 1 ELSE 0 END AS is_active
FROM monthly_ride_days
),
three_month_window AS (
SELECT
user_id,
ride_month,
is_active,
LAG(is_active, 1) OVER (PARTITION BY user_id ORDER BY ride_month) AS m_minus_1,
LAG(is_active, 2) OVER (PARTITION BY user_id ORDER BY ride_month) AS m_minus_2
FROM monthly_flags
)
SELECT
distinct user_id,
CASE
WHEN MAX(
CASE WHEN m_minus_2 = 1 AND m_minus_1 = 1 AND is_active = 1 THEN 1 ELSE 0 END
) OVER (PARTITION BY user_id) = 1
THEN 1
ELSE 0
END AS active_tag
FROM three_month_window
GROUP BY user_id, ride_month, is_active, m_minus_1, m_minus_2
order by user_id;
2025-01-31 Halo出行-通勤活跃用户标签开发  
WITH commute_rides AS (
SELECT
r.user_id,
DATE_FORMAT(r.start_time, '%Y-%m-01') AS ride_month,
DATE(r.start_time) AS ride_date
FROM hello_bike_riding_rcd r
JOIN gd_loc_map m1 ON r.start_loc = m1.loc_nm
JOIN gd_loc_map m2 ON r.end_loc = m2.loc_nm
WHERE (
(m1.loc_type = '地铁站' AND m2.loc_type = '写字楼')
OR (m1.loc_type = '写字楼' AND m2.loc_type = '地铁站')
)
AND r.start_time BETWEEN '2020-01-01' AND '2024-12-31'
),
monthly_ride_days AS (
SELECT
user_id,
ride_month,
COUNT(DISTINCT ride_date) AS commute_days
FROM commute_rides
GROUP BY user_id, ride_month
),
monthly_flags AS (
SELECT
user_id,
ride_month,
CASE WHEN commute_days >= 5 THEN 1 ELSE 0 END AS is_active
FROM monthly_ride_days
),
three_month_window AS (
SELECT
user_id,
ride_month,
is_active,
LAG(is_active, 1) OVER (PARTITION BY user_id ORDER BY ride_month) AS m_minus_1,
LAG(is_active, 2) OVER (PARTITION BY user_id ORDER BY ride_month) AS m_minus_2
FROM monthly_flags
)
SELECT
distinct user_id,
CASE
WHEN MAX(
CASE WHEN m_minus_2 = 1 AND m_minus_1 = 1 AND is_active = 1 THEN 1 ELSE 0 END
) OVER (PARTITION BY user_id) = 1
THEN 1
ELSE 0
END AS active_tag
FROM three_month_window
GROUP BY user_id, ride_month, is_active, m_minus_1, m_minus_2;
2025-01-28 窗口函数（7）三天吃四餐，你特么是不是乔杉？  
with c1 as (
select
	usr_id,
mch_nm,
trx_time,
lag(trx_time, 3)over(partition by usr_id order by trx_time) as date_3
from
	cmb_usr_trx_rcd
where
	mch_nm = "红玫瑰按摩保健休闲"),
c2 as(
	select
		usr_id,
	mch_nm,
	trx_time,
		datediff(trx_time,date_3) as datediff1
	from
		c1)
select
distinct usr_id
from
c2
where
datediff1<=3
order by
usr_id
2025-01-28 窗口函数（7）三天吃四餐，你特么是不是乔杉？  
WITH visits AS (
SELECT 
usr_id,
trx_time
FROM cmb_usr_trx_rcd
WHERE mch_nm = '红玫瑰按摩保健休闲'
),
windowed AS (
SELECT
usr_id,
trx_time,
COUNT(*) OVER (
PARTITION BY usr_id
ORDER BY trx_time
RANGE BETWEEN INTERVAL 3 DAY PRECEDING AND CURRENT ROW
) AS visits_in_3days
FROM visits
)
SELECT DISTINCT usr_id
FROM windowed
WHERE visits_in_3days >= 4
ORDER BY usr_id;
2024-12-14 大结局（😊）渣男9月爽翻天，罪证送他去西天  
WITH date_range AS (
SELECT 
date_value
FROM 
date_table
WHERE 
date_value BETWEEN '2024-09-01' AND '2024-09-30'
),
filtered_transactions AS (
SELECT
usr_id,
DATE(trx_time) AS trx_date,
trx_time,
trx_amt,
CASE
WHEN trx_amt = 288 THEN 'WithHand'
WHEN trx_amt = 388 THEN 'WithBalls'
WHEN trx_amt = 588 THEN 'BlowJobbie'
WHEN trx_amt = 888 THEN 'Doi'
WHEN trx_amt = 1288 THEN 'DoubleFly'
END AS action
FROM
cmb_usr_trx_rcd
WHERE
usr_id = 5201314520
AND mch_nm RLIKE '按摩|保健|休闲|会所'
AND DATE(trx_time) BETWEEN '2024-09-01' AND '2024-09-30'
),
transaction_summary AS (
SELECT
trx_date AS dt,
COUNT(*) AS FvckCnt,
SUM(CASE WHEN trx_amt = 288 THEN 1 ELSE 0 END) AS WithHand,
SUM(CASE WHEN trx_amt = 388 THEN 1 ELSE 0 END) AS WithBalls,
SUM(CASE WHEN trx_amt = 588 THEN 1 ELSE 0 END) AS BlowJobbie,
SUM(CASE WHEN trx_amt = 888 THEN 1 ELSE 0 END) AS Doi,
SUM(CASE WHEN trx_amt = 1288 THEN 1 ELSE 0 END) AS DoubleFly
FROM
filtered_transactions
GROUP BY
trx_date
),
special_transactions AS (
SELECT DISTINCT
t1.trx_date AS dt
FROM
filtered_transactions t1
JOIN
filtered_transactions t2
ON
t1.usr_id = t2.usr_id
AND t1.trx_date = t2.trx_date
AND t1.action = 'Doi'
AND t2.action = 'DoubleFly'
AND t1.trx_time < t2.trx_time
),
final_result AS (
SELECT
dr.date_value AS date_value,
COALESCE(ts.FvckCnt, 0) AS FvckCnt,
COALESCE(ts.WithHand, 0) AS WithHand,
COALESCE(ts.WithBalls, 0) AS WithBalls,
COALESCE(ts.BlowJobbie, 0) AS BlowJobbie,
COALESCE(ts.Doi, 0) AS Doi,
COALESCE(ts.DoubleFly, 0) AS DoubleFly,
CASE WHEN st.dt IS NOT NULL THEN 1 ELSE 0 END AS Ohya
FROM
date_range dr
LEFT JOIN
transaction_summary ts
ON
dr.date_value = ts.dt
LEFT JOIN
special_transactions st
ON
dr.date_value = st.dt
)
SELECT
date_value,
FvckCnt,
WithHand,
WithBalls,
BlowJobbie,
Doi,
DoubleFly,
Ohya
FROM
final_result
ORDER BY
date_value;
2024-12-14 小宇宙电台的同期群分析  
WITH login_data AS (
SELECT
usr_id,
DATE(login_time) AS login_date
FROM
user_login_log
),
user_first_login AS (
SELECT
usr_id,
MIN(login_date) AS first_login_date
FROM
login_data
GROUP BY
usr_id
),
user_previous_login AS (
SELECT
l.usr_id,
l.login_date,
MAX(CASE WHEN ld.login_date < l.login_date THEN ld.login_date ELSE NULL END) AS last_login_date
FROM
login_data l
LEFT JOIN
login_data ld
ON
l.usr_id = ld.usr_id
GROUP BY
l.usr_id, l.login_date
),
user_classification AS (
SELECT
l.usr_id,
l.login_date,
u.first_login_date,
p.last_login_date,
CASE
WHEN l.login_date = u.first_login_date THEN 'new_user'
WHEN p.last_login_date BETWEEN DATE_SUB(l.login_date, INTERVAL 3 DAY) AND DATE_SUB(l.login_date, INTERVAL 1 DAY) THEN 'retained_user'
WHEN p.last_login_date <= DATE_SUB(l.login_date, INTERVAL 4 DAY) THEN 'returning_user'
END AS user_type
FROM
login_data l
INNER JOIN
user_first_login u
ON
l.usr_id = u.usr_id
LEFT JOIN
user_previous_login p
ON
l.usr_id = p.usr_id AND l.login_date = p.login_date
),
daily_counts AS (
SELECT
login_date,
user_type,
COUNT(DISTINCT usr_id) AS user_count
FROM
user_classification
WHERE
user_type IS NOT NULL
AND login_date BETWEEN '2024-01-01' AND '2024-12-31'
GROUP BY
login_date, user_type
),
daily_totals AS (
SELECT
login_date,
SUM(user_count) AS total_users
FROM
daily_counts
GROUP BY
login_date
),
daily_percentages AS (
SELECT
d.login_date,
d.user_type,
ROUND((d.user_count / t.total_users) * 100, 2) AS pct
FROM
daily_counts d
INNER JOIN
daily_totals t
ON
d.login_date = t.login_date
),
formatted_output AS (
SELECT
login_date,
GROUP_CONCAT(
ROUND(pct, 2) ORDER BY FIELD(user_type, 'new_user', 'retained_user', 'returning_user') SEPARATOR ', '
) AS pct
FROM
daily_percentages
GROUP BY
login_date
)
SELECT
login_date,
pct
FROM
formatted_output
ORDER BY
login_date;
2024-12-11 快手面试真题（3）同时在线人数峰值时点  
WITH UserActivity AS (
    SELECT 
        t1.usr_id,
        t1.live_id,
        t1.enter_time AS event_time,
        1 AS act
    FROM 
        ks_live_t1 t1
    UNION ALL
    SELECT 
        t1.usr_id,
        t1.live_id,
        t1.leave_time AS event_time,
        -1 AS act
    FROM 
        ks_live_t1 t1
),
CumulativeOnline AS (
    SELECT 
        live_id,
        event_time,
        SUM(act) OVER (PARTITION BY live_id ORDER BY event_time) AS online_users
    FROM 
        UserActivity
),
PeakOnline AS (
    SELECT 
        live_id,
        event_time,
        online_users,
        MAX(online_users) OVER (PARTITION BY live_id) AS max_online_users
    FROM 
        CumulativeOnline
),
FirstLastPeak AS (
    SELECT 
        live_id,
        min(event_time) AS first_peak_time,
        max(event_time) AS last_peak_time,
        max_online_users
    FROM 
        PeakOnline
    WHERE 
        online_users = max_online_users
    GROUP BY 
        live_id, max_online_users
)
SELECT 
    flp.live_id,
    t2.live_nm,
    flp.max_online_users,
    flp.first_peak_time,
    flp.last_peak_time
FROM 
    FirstLastPeak flp
JOIN 
    ks_live_t2 t2
ON 
    flp.live_id = t2.live_id
ORDER BY 
    flp.max_online_users DESC;
2024-12-11 快手面试真题（3）同时在线人数峰值时点  
WITH UserActivity AS (
SELECT 
t1.usr_id,
t1.live_id,
t1.enter_time AS event_time,
1 AS act
FROM 
ks_live_t1 t1
UNION ALL
SELECT 
t1.usr_id,
t1.live_id,
t1.leave_time AS event_time,
-1 AS act
FROM 
ks_live_t1 t1
),
CumulativeOnline AS (
SELECT 
live_id,
event_time,
SUM(act) OVER (PARTITION BY live_id ORDER BY event_time) AS online_users
FROM 
UserActivity
),
PeakOnline AS (
SELECT 
live_id,
event_time,
online_users,
MAX(online_users) OVER (PARTITION BY live_id) AS max_online_users
FROM 
CumulativeOnline
),
FirstLastPeak AS (
SELECT 
live_id,
MAX(max_online_users) AS max_online_users,
MIN(CASE WHEN online_users = max_online_users THEN event_time ELSE NULL END) AS first_peak_time,
MAX(CASE WHEN online_users = max_online_users THEN event_time ELSE NULL END) AS last_peak_time
FROM 
PeakOnline
GROUP BY 
live_id
)
SELECT 
flp.live_id,
t2.live_nm,
flp.max_online_users,
flp.first_peak_time,
flp.last_peak_time
FROM 
FirstLastPeak flp
JOIN 
ks_live_t2 t2
ON 
flp.live_id = t2.live_id
ORDER BY 
flp.max_online_users DESC;