排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
没有收藏的题目。

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-04-28 抖音面试真题（1）T+1日留存率  
with daily_user as (
select distinct
usr_id,
date(login_time) as login_date
from user_login_log
where date(login_time) >= date_sub(curdate(), interval 30 day)
and date(login_time) < curdate()
)
select
login_date,
concat(
round(
sum(next_day_flag) / count( usr_id) * 100,
2
),
'%'
) as T1_retention_rate
from (
select
usr_id,
login_date,
case 
when lead(login_date) over (
partition by usr_id
order by login_date
) = date_add(login_date, interval 1 day)
then 1 else 0
end as next_day_flag
from daily_user
) t
group by login_date
order by login_date;
2026-04-28 抖音面试真题（1）T+1日留存率  
with daily_user as (
select distinct
usr_id,
date(login_time) as login_date
from user_login_log
where date(login_time) >= date_sub(curdate(), interval 30 day)
and date(login_time) < curdate()
)
select
login_date,
concat(
round(
sum(next_day_flag) / count(distinct usr_id) * 100,
2
),
'%'
) as T1_retention_rate
from (
select
usr_id,
login_date,
case 
when lead(login_date) over (
partition by usr_id
order by login_date
) = date_add(login_date, interval 1 day)
then 1 else 0
end as next_day_flag
from daily_user
) t
group by login_date
order by login_date;
2026-04-28 抖音面试真题（1）T+1日留存率  
with daily_user as (
select distinct
usr_id,
date(login_time) as login_date
from user_login_log
where date(login_time) >= date_sub(curdate(), interval 30 day)
and date(login_time) < curdate()
)
select
a.login_date,
concat(
round(
count( b.usr_id) / count( a.usr_id) * 100,
2
),
'%'
) as T1_retention_rate
from daily_user a
left join daily_user b
on a.usr_id = b.usr_id
 and b.login_date = date_add(a.login_date, interval 1 day)
group by a.login_date
order by a.login_date;
2026-04-27 抖音面试真题（1）T+1日留存率  
with daily_user as (
select distinct
usr_id,
date(login_time) as login_date
from user_login_log
where date(login_time) >= date_sub(curdate(), interval 30 day)
and date(login_time) < curdate()
)
select
a.login_date,
concat(
round(
count(distinct b.usr_id) / count(distinct a.usr_id) * 100,
2
),
'%'
) as T1_retention_rate
from daily_user a
left join daily_user b
on a.usr_id = b.usr_id
 and b.login_date = date_add(a.login_date, interval 1 day)
group by a.login_date
order by a.login_date;
2026-04-27 登录天数分布  
select
count(
case
when login_days between 1 and 5then 1
end
) as days_1_to_5,
count(
case
when login_days > 5
and login_days <= 10 then 1
end
) as days_6_to_10,
count(
case
when login_days > 10
and login_days <= 20 then 1
end
) as days_11_to_20,
count(
case
when login_days > 20 then 1
end
) as days_over_20
from (
select usr_id, count(distinct date(login_time)) as login_days
from user_login_log
where
login_time >= curdate() - interval 180 day
group by
usr_id
) t;
2026-04-27 通勤、午休、临睡个时间段活跃人数分布  
select
case
when time(login_time) between '07:30:00' and '09:30:00'
or time(login_time) between '18:30:00' and '20:30:00'
then '通勤'
when time(login_time) between '11:30:00' and '14:00:00'
then '午休'
when time(login_time) >= '22:30:00'
or time(login_time) <= '01:00:00'
then '临睡'
end as time_type,
count(distinct usr_id) as user_cnt
from user_login_log
where date(login_time) >= date_format(curdate() - interval 1 month, '%Y-%m-01')
and date(login_time) <= last_day(curdate() - interval 1 month)
group by time_type;
2026-04-27 通勤、午休、临睡个时间段活跃人数分布  
select
count(
distinct case
when time(login_time) between '07:30:00' and '09:30:00'
or time(login_time) between '18:30:00' and '20:30:00'then usr_id
end
) as commute,
count(
distinct case
when time(login_time) between '11:30:00' and '14:00:00'then usr_id
end
) as lunch_break,
count(
distinct case
when time(login_time) >= '22:30:00'
or time(login_time) <= '01:00:00' then usr_id
end
) as bedtime
from user_login_log
where
date(login_time) >= date_format(
curdate() - interval 1 month,
'%Y-%m-01'
)
and date(login_time) <= last_day(curdate() - interval 1 month);
2026-04-27 不分类别的最火直播间  
SELECT a.live_id, b.live_nm, COUNT(*) as `enter_cnt`
FROM ks_live_t1 a
JOIN ks_live_t2 b on a.live_id = b.live_id
WHERE
DATE(a.enter_time) = '2021-09-12'
and HOUR(a.enter_time) = '23'
GROUP BY
a.live_id,
b.live_nm
ORDER BY 3 DESC
LIMIT 5;
2026-04-27 不分类别的最火直播间  
SELECT a.live_id, b.live_nm, COUNT(*) as `enter_cnt`
FROM ks_live_t1 a
JOIN ks_live_t2 b on a.live_id = b.live_id
WHERE
DATE(a.enter_time) = '2021-09-12'
and HOUR(a.enter_time) = '23'
GROUP BY
a.live_id,
b.live_nm
ORDER BY 3 DESC;
2026-04-27 不分类别的最火直播间  
SELECT a.live_id, b.live_nm, COUNT(*)
FROM ks_live_t1 a
JOIN ks_live_t2 b on a.live_id = b.live_id
WHERE
DATE(a.enter_time) = '2021-09-12'
and HOUR(a.enter_time) = '23'
GROUP BY
a.live_id,
b.live_nm
ORDER BY 3 DESC;
2026-04-27 抖音面试真题（1）T+1日留存率  
with daily_user as (
select distinct usr_id, date(login_time) as login_date
from user_login_log
where date(login_time)>=curdate()- interval 30 day and date(login_time) < curdate()
)
select a.login_date, concat(
round(
count(distinct b.usr_id) / count(distinct a.usr_id) * 100, 2
), '%'
) as T1_retention_rate
from
daily_user a
left join daily_user b on a.usr_id = b.usr_id
and b.login_date = a.login_date + interval 1 day
group by
a.login_date
order by a.login_date
2026-04-26 查询播放量为0的歌手及其专辑  
SELECT 
    s.singer_id,
    s.singer_name,
    a.album_id,
    a.album_name,
    COUNT(l.id) AS play_count
FROM 
    singer_info s
JOIN 
    album_info a ON s.singer_id = a.singer_id
LEFT JOIN 
    song_info sg ON a.album_id = sg.album_id
LEFT JOIN 
    listen_rcd l ON sg.song_id = l.song_id
GROUP BY 
    s.singer_id, s.singer_name, a.album_id, a.album_name
HAVING 
    play_count = 0;
2026-04-26 用户听歌习惯的时间分布  
SELECT a.user_id, DAYNAME(b.start_time) as day_of_week, COUNT(*)
from
qqmusic_user_info a
join listen_rcd b ON a.user_id = b.user_id
GROUP BY
a.user_id,
day_of_week
2026-04-26 数学成绩分段统计（1）  
SELECT
CASE
WHEN score >= 110 THEN '[110, 120]'
WHEN score >= 90 THEN '[90, 110)'
WHEN score >= 60 THEN '[60, 90)'
ELSE '[0, 60)'
END AS score_range,
COUNT(*) as num_students
from students s
left join scores sc on s.student_id = sc.student_id
where
sc.exam_date = '2024-6-30'
and sc.subject = '数学'
GROUP BY
score_range
order by 2 desc
2026-04-26 不分类别的最火直播间  
SELECT 
t1.live_id,
t2.live_nm,
COUNT(*) AS enter_cnt
FROM ks_live_t1 t1
JOIN ks_live_t2 t2 
ON t1.live_id = t2.live_id
WHERE 
 DATE_FORMAT(t1.enter_time, '%Y-%m-%d %H') = '2021-09-12 23'
GROUP BY 
t1.live_id, t2.live_nm
ORDER BY 
enter_cnt DESC
LIMIT 5;
2026-04-26 不分类别的最火直播间  
SELECT 
t1.live_id,
t2.live_nm,
COUNT(*) AS enter_cnt
FROM ks_live_t1 t1
JOIN ks_live_t2 t2 
ON t1.live_id = t2.live_id
WHERE 
t1.enter_time <= '2021-09-12 23:00:00'
AND t1.leave_time >= '2021-09-12 23:00:00'
GROUP BY 
t1.live_id, t2.live_nm
ORDER BY 
enter_cnt DESC
LIMIT 5;
2026-04-26 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
select a.mch_nm as asshole_tried,a.trx_cnt, b.mch_nm as darling_tried from 
(select mch_nm, count(1) trx_cnt
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5201314520'
group by mch_nm
having count(1) >=20)a
left join
(select distinct mch_nm
from cmb_usr_trx_rcd
where year(trx_time) in (2023,2024) and usr_id='5211314521')b
on a.mch_nm = b.mch_nm order by 2 desc
2026-04-26 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
SELECT 
a.mch_nm AS asshole_tried,
a.trx_cnt,
0 AS darling_tried
FROM (
SELECT mch_nm, COUNT(*) AS trx_cnt
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND trx_time >= '2023-01-01'
AND trx_time < '2025-01-01'
GROUP BY mch_nm
HAVING COUNT(*) >= 20
) a
WHERE NOT EXISTS (
SELECT 1
FROM cmb_usr_trx_rcd b
WHERE b.mch_nm = a.mch_nm
AND b.usr_id = 5211314521
AND b.trx_time >= '2023-01-01'
AND b.trx_time < '2025-01-01'
)
ORDER BY a.trx_cnt DESC;
2026-04-26 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
SELECT 
a.mch_nm AS asshole_tried,
a.trx_cnt,
0 AS darling_tried
FROM (
SELECT mch_nm, COUNT(*) AS trx_cnt
FROM cmb_usr_trx_rcd
WHERE usr_id = 5201314520
AND trx_time >= '2023-01-01'
AND trx_time < '2025-01-01'
GROUP BY mch_nm
HAVING COUNT(*) >= 20
) a
LEFT JOIN (
SELECT DISTINCT mch_nm
FROM cmb_usr_trx_rcd
WHERE usr_id = 5211314521
AND trx_time >= '2023-01-01'
AND trx_time < '2025-01-01'
) b
ON a.mch_nm = b.mch_nm
WHERE b.mch_nm IS NULL
ORDER BY a.trx_cnt DESC;
2026-04-26 表连接（2）渣男去过我对象没去过，那就用LeftJoin  
select DISTINCT
a.mch_nm AS asshole_tried,
a.trx_cnt
from (
select mch_nm, count(*) as trx_cnt
from cmb_usr_trx_rcd
WHERE
usr_id = 5201314520
and YEAR(trx_time) BETWEEN 2023 and 2024
GROUP BY
mch_nm
HAVING
COUNT(mch_nm) >= 20
) a
LEFT JOIN (
select mch_nm
from cmb_usr_trx_rcd
WHERE
usr_id = 5201314521
and YEAR(trx_time) BETWEEN 2023 and 2024
) b ON a.mch_nm = b.mch_nm
WHERE
b.mch_nm IS NULL
ORDER BY a.trx_cnt DESC;