全站第 24/1365 名

解决了 78/335 题

中等: 21/76
入门: 20/77
困难: 11/29
简单: 25/114
草履虫: 1/39

过去1年一共提交 310 次

Nov
Dec
Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct

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

达成1题里程碑2025-01-22
达成2题里程碑2025-01-22
达成5题里程碑2025-02-06
达成10题里程碑2025-02-07
达成20题里程碑2025-02-08
达成50题里程碑2025-03-14
完成w10打卡2025-03-22
完成w23打卡2025-03-15
完成w34打卡2025-03-22
完成w37打卡2025-03-22
完成w39打卡2025-03-13
完成w40打卡2025-05-23

收藏

收藏日期 题目名称 解决状态
2025-03-26 计算完播率（按人数）  已解决
2025-03-22 从商品角度统计收藏到购买的转化率  已解决
2025-03-22 快手面试真题（3）同时在线人数峰值时点  已解决
2025-03-22 抖音面试真题（6）人数最多的姓氏  已解决
2025-03-22 通勤、午休、临睡个时间段活跃人数分布  已解决
2025-03-22 抖音面试真题（5）新用户的T+1月留存  已解决
2025-03-22 抖音面试真题（4）T+1月留存  已解决
2025-03-22 10月1日后再也没活跃过的用户  已解决
2025-03-15 分类别人均在线时长最火直播间  未解决
2025-03-14 只买iPhone的用户  已解决
2025-03-14 全量用户标签表  已解决
2025-03-14 给商品打四类标签（行）  已解决
2025-02-16 抖音面试真题（2）一次性计算T+1、T+3、T+7、T+14留存率  已解决
2025-02-11 抖音面试真题（1）T+1日留存率  已解决
2025-02-09 9分客户最爱去的Top3餐厅  已解决
2025-02-09 比较每个月客户的拉新质量（2）  已解决
2025-02-08 快手面试真题（2）同时在线人数峰值  已解决
2025-02-08 连续登录3天及以上  已解决
2025-02-07 窗口函数（7）三天吃四餐，你特么是不是乔杉？  已解决
2025-02-07 分类（1）姿势太多很过分，分类要用CaseWhen  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-03-22 抖音面试真题（5）新用户的T+1月留存  
同意
 
所以要怎么改呢？把第一个cte的时间日期过滤条件去掉嘛，😏
2025-03-15 分类别人均在线时长最火直播间  
答案没有算23点前进入，23点至4点离开的，以及23后进入，4点后离开的，我的答案是：
with live as (
select 
	t1.live_id,
    t2.live_nm,
    t2.live_type,
  	t1.usr_id,
    case 
    	when t1.enter_time between '2021-09-12 23:00:00' and '2021-09-13 03:59:59' then t1.enter_time
        when t1.enter_time < '2021-09-12 23:00:00' then '2021-09-12 23:00:00'  end as start,
    case 
    	when t1.leave_time between '2021-09-12 23:00:00' and '2021-09-13 03:59:59' then t1.leave_time
        when t1.leave_time > '2021-09-13 03:59:59' then '2021-09-13 03:59:59' end as end
    
from
	ks_live_t1 t1
left join
	ks_live_t2 t2 on t1.live_id = t2.live_id
where 
	t1.enter_time <= '2021-09-13 03:59:59' and t1.leave_time >= '2021-09-12 23:00:00' 
)

select
	live_id,
    live_nm,
    live_type,
    sum(timestampdiff(second,start,end)) as total_duration,
    count(distinct usr_id) as total_users,
    avg(timestampdiff(second,start,end)) as avg_duration
from 
	live
group by
	live_id,
    live_nm,
    live_type
order by 
	live_id
。
 
啥也没说
2025-02-25 抖音面试真题（2）一次性计算T+1、T+3、T+7、T+14留存率  
输出里写的90天，题目中写的30天

啥也没说
2025-02-16 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  
为什么是between 2 and 3 而不是 1 and 3
 
啥也没说

提交记录

提交日期 题目名称 提交代码
2025-07-22 条件过滤（1）异地男友有异常，数分闺蜜来帮忙  
select 
*
from cmb_usr_trx_rcd
where date(trx_time) between '2024-09-01' and '2024-09-30'
and usr_id = '5201314520'
order by 
trx_time
2025-07-22 条件过滤（1）异地男友有异常，数分闺蜜来帮忙  
select
    *
from
    cmb_usr_trx_rcd
where 
    date(trx_time) between '2024-09-01' and '2024-09-30'
    and usr_id='5201314520'
order by 
    trx_time
2025-07-02 字符串与通配符（1）名称里面有特服，可以使用通配符  
select count(distinct mch_nm) as mch_cnt
from cmb_usr_trx_rcd
where mch_nm like '%按摩保健休闲'
2025-05-23 聚合函数-735011学生的语文成绩  
SELECT 
    MAX(score) AS max_score, 
    MIN(score) AS min_score, 
    AVG(score) AS avg_score
FROM scores
WHERE student_id = 735011 AND subject = '语文';
2025-05-23 条件过滤-查找1994年至1997年毕业的女教师  
select name, class_code, subject, graduate_date
 from teachers
 where graduate_date between "1994-01-01" and "1997-12-31" and gender ="f"
 order by graduate_date asc;
2025-05-22 窗口函数（1）年度前三和每月前三，搞懂排序窗口函数  
with yearly_top_merchants as (
    select 
        mch_nm, 
        sum(trx_amt) as sum_trx_amt
    from 
        cmb_usr_trx_rcd
    where 
        usr_id = 5201314520 
        and year(trx_time) = 2024
    group by 
        mch_nm
    order by 
        sum_trx_amt desc
    limit 3
),
monthly_top_merchants as (
    select 
        date_format(trx_time, '%Y-%m') as trx_mon, 
        mch_nm, 
        sum(trx_amt) as sum_trx_amt,
        row_number() over (partition by date_format(trx_time, '%Y-%m') order by sum(trx_amt) desc) as rn
    from 
        cmb_usr_trx_rcd
    where 
        usr_id = 5201314520 
        and year(trx_time) = 2024
    group by 
        trx_mon, mch_nm
),
filtered_monthly_top_merchants as (
    select 
        trx_mon, 
        mch_nm, 
        sum_trx_amt
    from 
        monthly_top_merchants
    where 
        rn <= 3
)
select 
    '2024' as trx_mon, 
    mch_nm, 
    sum_trx_amt
from 
    yearly_top_merchants
union all
select 
    trx_mon, 
    mch_nm, 
    sum_trx_amt
from 
    filtered_monthly_top_merchants
order by 
    trx_mon, 
    sum_trx_amt desc;
2025-05-21 抖音面试真题（1）T+1日留存率  
select * from user_login_log limit 5;
2025-05-21 连续登录3天及以上  
with daily_login as (
select
usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date, date(login_time)) <= 30
group by
usr_id, login_date
),
ranked_logins as (
select
usr_id,
login_date,
row_number()over(partition by usr_id order by login_date) as row_num
from
daily_login
),
grouped_logins as (
select
usr_id,
login_date,
login_date - interval row_num day as grp
from
ranked_logins
),
consecutive_logins as (
select
usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(*) as consecutive_days
from
grouped_logins
group by
usr_id, grp
having
count(*) > 2
)
select
	usr_id,
start_date,
end_date,
consecutive_days
from
	consecutive_logins
order by
	usr_id asc,
start_date
2025-05-21 连续登录3天及以上  
with daily_login as (
select
usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(date(login_time), current_date)
group by
usr_id, login_date
),
ranked_logins as (
select
usr_id,
login_date,
row_number()over(partition by usr_id order by login_date) as row_num
from
daily_login
),
grouped_logins as (
select
usr_id,
login_date,
login_date - interval row_num day as grp
from
ranked_logins
),
consecutive_logins as (
select
usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(*) as consecutive_days
from
grouped_logins
group by
usr_id, grp
having
count(*) > 2
)
select
	usr_id,
start_date,
end_date,
consecutive_days
from
	consecutive_logins
order by
	usr_id asc,
start_date
2025-04-09 连续登录3天及以上  
with daily_user_login as (
select
usr_id,
date(login_time) as login_date
from
user_login_log
where
datediff(current_date, date(login_time)) <= 30
group by
usr_id,date(login_time)
),
rnk as (
select
usr_id,
login_date,
row_number()over(partition by usr_id order by login_date) as rnm
from
daily_user_login
),
grp as (
select
usr_id,
login_date,
login_date - rnm as grp
from
rnk
),
consecutive_days as (
select
usr_id,
min(login_date) as start_date,
max(login_date) as end_date,
count(*) as cd
from
grp
group by
usr_id, grp
having
count(*) >= 3
)
select * from consecutive_days
2025-03-26 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  
with daily_user_login as (
select
	usr_id,
date(login_time) as login_date
from
	user_login_log
where
	datediff(current_date , date(login_time)) <= 90
group by
usr_id, login_date
), retention as (
select
t1.usr_id,
t1.login_date as login_date,
t2.login_date as next_login_date
from
daily_user_login t1
left join
daily_user_login t2 on t1.usr_id = t2.usr_id and datediff(t2.login_date, t1.login_date) between 1 and 14
)
select
	login_date,
count(distinct case when datediff(next_login_date, login_date) <= 3 then usr_id end) / count(distinct usr_id),
count(distinct case when datediff(next_login_date, login_date) <= 7 then usr_id end) / count(distinct usr_id),
count(distinct case when datediff(next_login_date, login_date) <= 14 then usr_id end) / count(distinct usr_id)
from
	retention
group by
	login_date
2025-03-26 计算完播率（按人数）  
SELECT 
    v.video_id, 
    v.title, 
    ROUND(
        (COUNT(DISTINCT CASE WHEN TIMESTAMPDIFF(SECOND, u.start_time, u.end_time) >= v.duration THEN u.uid END) / 
         COUNT(DISTINCT u.uid)) * 100, 
        4
    ) AS completion_rate
FROM 
    ks_video_inf v
JOIN 
    ks_video_wat_log u ON v.video_id = u.video_id
WHERE 
    u.start_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY 
    v.video_id, v.title
ORDER BY 
    completion_rate DESC
LIMIT 5;
2025-03-26 连续登录3天及以上  
with login_d as (
select
	usr_id,
date(login_time) as login_date
from
	user_login_log
where
datediff(current_date,date(login_time)) <= 30
group by
	usr_id, login_date
),
grouped as (
select
usr_id,
login_date,
login_date - interval row_number()over(partition by usr_id order by login_date) day as grp
from
login_d
)
select
usr_id,
min(login_date),
max(login_date),
count(*)
from
	grouped
group by
	usr_id, grp
having count(*) >= 3
2025-03-26 窗口函数（7）三天吃四餐，你特么是不是乔杉？  
with f_t as (
select 
usr_id, 
trx_time,
trx_amt,
mch_nm,
lag(trx_time, 3) over (partition by usr_id order by trx_time) as prev_3_trx_time
from 
cmb_usr_trx_rcd
where 
mch_nm = '红玫瑰按摩保健休闲'
)
select
distinct usr_id
from
f_t
where
datediff(trx_time, prev_3_trx_time) <= 3
2025-03-23 抖音面试真题（2）一次性计算T+1、T+3、T+7、T+14留存率  
with daily_login as (
select
usr_id,
date(login_time) as login_date
from
user_login_log
where 
datediff(current_date, date(login_time)) <= 30
group by
usr_id, date(login_time)
), 
t2 as (
select
t1.usr_id,
t1.login_date,
datediff(t2.login_date, t1.login_date) as date_diff
from
daily_login t1
left join daily_login t2 on t1. usr_id = t2.usr_id and t2.login_date > t1.login_date
),
retention as (
select
login_date,
round(count(distinct case when date_diff = 1 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_1_retention_rate,
round(count(distinct case when date_diff = 3 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_3_retention_rate,
round(count(distinct case when date_diff = 7 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_7_retention_rate,
round(count(distinct case when date_diff = 14 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_14_retention_rate
from
t2
group by
login_date
)
select * from retention
2025-03-23 抖音面试真题（2）一次性计算T+1、T+3、T+7、T+14留存率  
with daily_login as (
select
usr_id,
date(login_time) as login_date
from
user_login_log
where 
datediff(current_date, date(login_time)) <= 30
group by
usr_id, date(login_time)
), 
t2 as (
select
t1.usr_id,
t1.login_date,
datediff(t2.login_date, t1.login_date) as date_diff
from
daily_login t1
join daily_login t2 on t1. usr_id = t2.usr_id and t2.login_date > t1.login_date
),
retention as (
select
login_date,
round(count(distinct case when date_diff = 1 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_1_retention_rate,
round(count(distinct case when date_diff = 3 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_3_retention_rate,
round(count(distinct case when date_diff = 7 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_7_retention_rate,
round(count(distinct case when date_diff = 14 then usr_id end) / count(distinct usr_id) * 100,2) as t_plus_14_retention_rate
from
t2
group by
login_date
)
select * from retention
2025-03-23 快手面试真题（3）同时在线人数峰值时点  
with action as (
select
	usr_id,
live_id,
enter_time as act_time,
1 as act
from
	ks_live_t1
union all
select
	usr_id,
live_id,
leave_time as act_time,
-1 as act
from
	ks_live_t1
),
num as (
select
live_id,
act_time,
sum(act) over(partition by live_id order by act_time) as cnt
from
action
), 
max_num as(
select 
	live_id,
act_time,
	cnt,
max(cnt) over (partition by live_id) as max_cnt
from 
	num
)
select
	t1.live_id,
t2.live_nm,
t1.max_cnt,
min(t1.act_time),
max(t1.act_time)
from
	max_num t1
left join
	ks_live_t2 t2 on t1.live_id = t2.live_id
where t1.cnt = t1.max_cnt
group by
	t1.live_id, t2.live_nm, t1.max_cnt
order by
	t1.max_cnt desc
2025-03-23 快手面试真题（3）同时在线人数峰值时点  
with action as (
select
	usr_id,
live_id,
enter_time as act_time,
1 as act
from
	ks_live_t1
union all
select
	usr_id,
live_id,
leave_time as act_time,
-1 as act
from
	ks_live_t1
),
num as (
select
live_id,
act_time,
sum(act) over(partition by live_id order by act_time) as cnt
from
action
), max_num as(
select 
	live_id,
act_time,
max(cnt) over (partition by live_id) as cnt
from 
	num
)
select
	t1.live_id,
t2.live_nm,
t1.cnt,
min(t1.act_time),
max(t1.act_time)
from
	max_num t1
left join
	ks_live_t2 t2 on t1.live_id = t2.live_id
group by
	t1.live_id, t2.live_nm, t1.cnt
order by
	t1.cnt desc
2025-03-23 快手面试真题（3）同时在线人数峰值时点  
with action as (
select
	usr_id,
live_id,
enter_time as act_time,
1 as act
from
	ks_live_t1
union all
select
	usr_id,
live_id,
leave_time as act_time,
-1 as act
from
	ks_live_t1
),
num as (
select
live_id,
act_time,
sum(act) over(partition by live_id order by act_time) as cnt
from
action
), max_num as(
select 
	live_id,
act_time,
max(cnt) over (partition by live_id) as cnt
from 
	num
)
select
	t1.live_id,
t2.live_nm,
t1.cnt,
min(t1.act_time),
max(t1.act_time)
from
	max_num t1
left join
	ks_live_t2 t2 on t1.live_id = t2.live_id
group by
	t1.live_id, t2.live_nm, t1.cnt
order by
	t1.cnt
2025-03-23 快手面试真题（2）同时在线人数峰值  
with action as (
select
	usr_id,
live_id,
enter_time as act_time,
1 as act
from
	ks_live_t1
union
select
	usr_id,
live_id,
leave_time as act_time,
-1 as act
from
	ks_live_t1
),
num as (
select
live_id,
sum(act) over(partition by live_id order by act_time) as cnt
from
action
)
select 
	num.live_id,
t2.live_nm,
max(num.cnt)
from 
	num
left join
	ks_live_t2 t2 on num.live_id = t2.live_id
group by
	num.live_id, t2.live_nm
order by
	max(num.cnt) desc