排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2026-01-25 多云天气天数  已解决
2026-01-24 抖音面试真题（1）T+1日留存率  已解决
2026-01-21 基础标量子查询-带分组  已解决
2026-01-21 表连接（2）渣男去过我对象没去过，那就用LeftJoin  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2026-01-31 抖音面试真题（1）T+1日留存率  
SELECT 
    u1.login_date,
    CONCAT(
        ROUND(
            COUNT(u2.usr_id) / COUNT(u1.usr_id) * 100, 
            2
        ), 
        '%'
    ) AS T1_retention_rate
FROM (
    SELECT 
        usr_id,
        DATE(login_time) AS login_date
    FROM user_login_log
    GROUP BY usr_id, DATE(login_time)
) u1
LEFT JOIN (
    SELECT 
        usr_id,
        DATE(login_time) AS login_date
    FROM user_login_log
    GROUP BY usr_id, DATE(login_time)
) u2 ON u1.usr_id = u2.usr_id 
    AND u2.login_date = DATE_ADD(u1.login_date, INTERVAL 1 DAY)
WHERE DATEDIFF(CURRENT_DATE, u1.login_date) <= 30
GROUP BY u1.login_date
 
啥也没说
2026-01-28 通勤、午休、临睡个时间段活跃人数分布  
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 login_time >= DATE_FORMAT(DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH), '%Y-%m-01 00:00:00')
  AND login_time < DATE_FORMAT(CURRENT_DATE, '%Y-%m-01 00:00:00');
 
啥也没说

提交记录

提交日期 题目名称 提交代码
2026-03-06 21世纪上市的银行  
select *
from stock_info
where year(list_date) >= 2000 and industry = '银行'
order by list_date
2026-03-06 21世纪上市的银行  
select *
from stock_info
where year(list_date) > 2000 and industry = '银行'
order by list_date
2026-02-02 输出地区为北京的所有银行  
select *
from stock_info
where area = '北京' and industry = '银行'
order by list_date
2026-02-02 海王发红包  
select snd_usr_id
from tx_red_pkt_rcd
where pkt_amt in (520,200)
group by snd_usr_id
having count(pkt_amt) >=5
2026-01-31 1989年12月13日出生的女歌手  
select *
from singer_info
where date(birth_date) = '1989-12-13'
2026-01-31 找出所有港台歌手  
select *
from singer_info
where type2 = '港台'
order by singer_id
2026-01-31 抖音面试真题（1）T+1日留存率  
select u1.login_date,
concat(round(count(u2.usr_id)/count(u1.usr_id)*100,2),'%') as T1_retention_rate
from(
select usr_id,
date(login_time) as login_date
from user_login_log
group by usr_id,date(login_time)) u1
left join(
select usr_id,
date(login_time) as login_date
from user_login_log
group by usr_id,date(login_time)) u2
on u1.usr_id = u2.usr_id and u2.login_date=date_add(u1.login_date,interval 1 day)
where datediff(current_date,u1.login_date)<=30
group by u1.login_date
2026-01-31 抖音面试真题（1）T+1日留存率  
SELECT 
u1.login_date,
CONCAT(
ROUND(
COUNT(u2.usr_id) / COUNT(u1.usr_id) * 100, 
2
), 
'%'
) AS T1_retention_rate
FROM (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
GROUP BY usr_id, DATE(login_time)
) u1
LEFT JOIN (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
GROUP BY usr_id, DATE(login_time)
) u2 ON u1.usr_id = u2.usr_id 
AND u2.login_date = DATE_ADD(u1.login_date, INTERVAL 1 DAY)
WHERE DATEDIFF(CURRENT_DATE, u1.login_date) <= 30
GROUP BY u1.login_date
;
2026-01-31 抖音面试真题（1）T+1日留存率  
SELECT 
u1.login_date,
CONCAT(
ROUND(
COUNT(u2.usr_id) / COUNT(u1.usr_id) * 100, 
2
), 
'%'
) AS T1_retention_rate
FROM (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
GROUP BY usr_id, DATE(login_time)
) u1
LEFT JOIN (
SELECT 
usr_id,
DATE(login_time) AS login_date
FROM user_login_log
GROUP BY usr_id, DATE(login_time)
) u2 ON u1.usr_id = u2.usr_id 
AND u2.login_date = DATE_ADD(u1.login_date, INTERVAL 1 DAY)
WHERE DATEDIFF(CURRENT_DATE, u1.login_date) <= 30
GROUP BY u1.login_date
ORDER BY u1.login_date;
2026-01-31 找出所有港台乐队  
select *
from singer_info
where type2 = '港台' and type3 = '乐队'
order by singer_id
2026-01-30 查询播放量为0的歌手及其专辑  
select 
si.singer_id,
si.singer_name,
ai.album_id,
ai.album_name,
count(lr.if_finished) as play_count
from singer_info si
join album_info ai on si.singer_id = ai.singer_id
left join song_info si2 on ai.album_id = si2.album_id
left join listen_rcd lr on si2.song_id = lr.song_id
group by si.singer_id, si.singer_name, ai.album_id, ai.album_name
having play_count = 0;
2026-01-30 用户听歌习惯的时间分布  
select lr.user_id,
dayname(lr.start_time) as day_of_week,
count(lr.if_finished) as listens_per_day
from listen_rcd lr 
join qqmusic_user_info qq on lr.user_id = qq.user_id 
group by lr.user_id, dayname(lr.start_time)
order by lr.user_id, day_of_week;
2026-01-30 用户听歌习惯的时间分布  
select qq.user_id,
dayname(lr.start_time) as day_of_week,
count(lr.if_finished) as listens_per_day
from listen_rcd lr 
join qqmusic_user_info qq on lr.user_id = qq.user_id 
group by qq.user_id, dayname(lr.start_time)
order by qq.user_id, day_of_week;
2026-01-30 特定歌曲的播放记录  
select *
from listen_rcd
where song_id = 13 and date(start_time) between '2023-12-10' and '2023-12-31'
order by start_time
2026-01-30 海王发红包  
select distinct snd_usr_id
from tx_red_pkt_rcd
where pkt_amt =520 or pkt_amt = 200
group by snd_usr_id
having count(pkt_amt) >=5
2026-01-30 总分超过300分的学生  
select student_id
from subject_score
where chinese + math + english >=300
2026-01-30 至少两门科目大于等于110分的学生  
select student_id,chinese,math,english
from subject_score
group by student_id
having chinese >=110 and math >=110
union 
select student_id,chinese,math,english
from subject_score
group by student_id
having chinese >=110 and english >=110
union 
select student_id,chinese,math,english
from subject_score
group by student_id
having math >=110 and english >=110
order by student_id
2026-01-30 至少两门科目大于等于110分的学生  
select student_id,chinese,math,english
from subject_score
group by student_id
having chinese >=110 and math >=110
union all
select student_id,chinese,math,english
from subject_score
group by student_id
having chinese >=110 and english >=110
union all
select student_id,chinese,math,english
from subject_score
group by student_id
having math >=110 and english >=110
order by student_id
2026-01-30 至少两门科目大于等于110分的学生  
select student_id,chinese,math,english
from subject_score
group by student_id
having sum(chinese) + sum(math) >=110
union all
select student_id,chinese,math,english
from subject_score
group by student_id
having sum(chinese) + sum(english) >=110
union all
select student_id,chinese,math,english
from subject_score
group by student_id
having sum(math) + sum(math) >=110
order by student_id
2026-01-30 至少两门科目大于等于110分的学生  
select student_id,chinese,math,english
from subject_score
group by student_id
having sum(chinese) + sum(math) >=110
union all
select student_id,chinese,math,english
from subject_score
group by student_id
having sum(chinese) + sum(english) >=110
union all
select student_id,chinese,math,english
from subject_score
group by student_id
having sum(math) + sum(math) >=110
order by chinese,math,english