右下角图片

排名

用户解题统计

过去一年提交了

勋章 ①金银铜：在竞赛中获得第一二三名；②好习惯：自然月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-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-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;
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
),
PeakTimes AS (
SELECT 
live_id,
event_time,
online_users,
max_online_users
FROM 
PeakOnline
WHERE 
online_users = max_online_users
),
FirstLastPeakTimes AS (
SELECT
live_id,
MIN(event_time) AS first_peak_time,
MAX(event_time) AS last_peak_time,
MAX(max_online_users) AS max_online_users
FROM
PeakTimes
GROUP BY
live_id
)
SELECT 
flpt.live_id,
t2.live_nm,
flpt.max_online_users,
flpt.first_peak_time,
flpt.last_peak_time
FROM 
FirstLastPeakTimes flpt
JOIN 
ks_live_t2 t2
ON 
flpt.live_id = t2.live_id
ORDER BY 
flpt.max_online_users DESC;
2024-12-11 快手面试真题（2）同时在线人数峰值  
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
),
PeakTimes AS (
SELECT 
live_id,
event_time AS peak_time,
max_online_users
FROM 
PeakOnline
WHERE 
online_users = max_online_users
)
SELECT 
pt.live_id,
t2.live_nm,
pt.max_online_users
FROM 
PeakTimes pt
JOIN 
ks_live_t2 t2
ON 
pt.live_id = t2.live_id
GROUP BY
pt.live_id,
t2.live_nm,
pt.max_online_users
ORDER BY 
pt.max_online_users DESC;
2024-12-11 快手面试真题（2）同时在线人数峰值  
WITH time_slices AS (
SELECT 
live_id,
UNIX_TIMESTAMP(enter_time) AS start_time,
UNIX_TIMESTAMP(leave_time) AS end_time
FROM 
ks_live_t1
),
expanded_times AS (
SELECT 
ts.live_id,
ts.start_time + seq.seq AS time_slice
FROM 
time_slices ts
JOIN (
SELECT 
@row := @row + 1 AS seq
FROM 
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) a,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) b,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) c,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) d,
(SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4) e
JOIN (SELECT @row := -1) r
) seq
WHERE ts.start_time + seq.seq <= ts.end_time
),
online_counts AS (
SELECT
live_id,
time_slice,
COUNT(*) AS online_users
FROM
expanded_times
GROUP BY
live_id, time_slice
),
peak_users AS (
SELECT 
live_id,
MAX(online_users) AS max_online_users
FROM 
online_counts
GROUP BY 
live_id
)
SELECT 
p.live_id,
t.live_nm,
p.max_online_users
FROM 
peak_users p
JOIN 
ks_live_t2 t
ON 
p.live_id = t.live_id
ORDER BY 
p.max_online_users DESC;
2024-12-11 滴滴面试真题（1）-打车订单应答率  
SELECT
COUNT(*) AS total_orders,
SUM(CASE WHEN grab_time != '1970-01-01 00:00:00' THEN 1 ELSE 0 END) AS answered_orders,
CONCAT(ROUND(SUM(CASE WHEN grab_time != '1970-01-01 00:00:00' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 2), '%') AS answer_rate
FROM
didi_order_rcd
WHERE
DATE(call_time) = '2021-05-03';
2024-12-11 招建银行信用卡中心客户挽留-电商平台分类  
SELECT DISTINCT
mch_nm AS merchant_name,
CASE
WHEN mch_nm REGEXP '拼多多' THEN '拼多多'
WHEN mch_nm REGEXP '京东' THEN '京东'
WHEN mch_nm REGEXP '淘宝|天猫' THEN '淘系'
WHEN mch_nm REGEXP '抖音' THEN '抖音'
WHEN mch_nm REGEXP '小红书' THEN '小红书'
ELSE '其他'
END AS platform
FROM
ccb_trx_rcd
ORDER BY
platform DESC;
2024-12-11 招建银行信用卡中心客户挽留-电商平台分类  
SELECT DISTINCT
mch_nm AS merchant_name,
CASE
WHEN mch_nm LIKE '%拼多多%' THEN '拼多多'
WHEN mch_nm LIKE '%京东%' THEN '京东'
WHEN mch_nm LIKE '%淘宝%' OR mch_nm LIKE '%天猫%' THEN '淘系'
WHEN mch_nm LIKE '%抖音%' THEN '抖音'
WHEN mch_nm LIKE '%小红书%' THEN '小红书'
ELSE '其他'
END AS platform
FROM
ccb_trx_rcd
ORDER BY
platform DESC;