排名

用户解题统计

过去一年提交了

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

错题集 数据思维刷题中答错的题目

模块 知识点 题目 你的答案 正确答案 操作
暂无错题，继续保持！

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-05-12 QQ音乐（四）歌手播放完成率  
select
 i.singer_name,
 sum(case when if_finished = 1 then 1 end) as finished_cnt,
 count(*) as total_cnt,
 round(sum(case when if_finished = 1 then 1 end)*100/ count(*),2) as finish_rate
 from
 listen_rcd l
 join
 song_info s 
 on
 l.song_id = s.song_id
 join
 singer_info i 
 on
 s.origin_singer_id = i.singer_id
group by
 i.singer_name
2026-05-12 QQ音乐（三）歌手平均播放量  
select 
i.singer_name,
count(distinct s.song_name) as song_cnt,
count(l.start_time) as total_listen,
 round(count(l.start_time)/count(distinct s.song_name),2) as avg_listen
from 
listen_rcd l 
join
song_info s 
on
l.song_id = s.song_id
join
singer_info i 
on
s.origin_singer_id = i.singer_id
group by
i.singer_name
order by
avg_listen desc
2026-05-12 QQ音乐（二）歌手歌曲数量  
select 
i.singer_name,
count(s.song_name) as song_cnt
from 
song_info s 
join
singer_info i
on
s.origin_singer_id = i.singer_id
group by
i.singer_name
order by
song_cnt desc
2026-05-12 QQ音乐（一）歌手播放量排行  
select 
 i.singer_name,
 count(start_time) as listen_cnt
from 
listen_rcd r 
join
song_info s
 on
 r.song_id = s.song_id 
 join
 singer_info i 
 on
 s.origin_singer_id = i.singer_id
 group by
 i.singer_name
 order by
 listen_cnt desc
 limit 5
2026-04-26 抖音面试真题（6）人数最多的姓氏  
select 
left(name,1) as first_name,
count(distinct student_id) as cnt
from 
students 
where
length(name) = 6
group by
first_name
order by
cnt desc
limit
3
2026-04-26 抖音面试真题（6）人数最多的姓氏  
select 
left(name,1) as first_name,
count(distinct student_id) as cnt
from 
students 
group by
first_name
order by
cnt desc
limit
3
2026-04-26 登录天数分布  
with a as(
select 
usr_id,
date(login_time) as login_date
from 
user_login_log 
where
datediff(current_date(),login_time) <= 180
),
b as(
select
usr_id,
count(distinct login_date) as login_days
from
a 
group by
usr_id
)
select
sum(case when login_days BETWEEN 1 AND 5 THEN 1 ELSE 0 END) as days_1_to_5,
sum(case when login_days BETWEEN 6 AND 10 THEN 1 ELSE 0 END) as days_6_to_10,
sum(case when login_days BETWEEN 11 AND 20 THEN 1 ELSE 0 END) as days_11_to_20,
sum(case when login_days > 20 THEN 1 ELSE 0 END) as days_20
from
b
2026-03-28 整体搜索UV转化率  
with a as(
select 
q.usr_id,
q.key_word,
c.click_page_id,
q.search_tm,
q.session_id
from 
jx_query_rcd q
left join
jx_click_rcd c 
on
q.usr_id = c.usr_id
and
q.session_id = c.session_id
)
select
count(distinct usr_id) as total_search_users,
count(distinct case when click_page_id is not null then usr_id end) as users_reached_product_page,
round(count(distinct case when click_page_id is not null then usr_id end)*100/count(distinct usr_id),2) as uv_conversion_rate
from
a
2026-03-28 连续登录3天及以上  
WITH user_login_days AS (
    SELECT 
        usr_id,
        DATE(login_time) AS login_date
    FROM 
        user_login_log
    WHERE 
        login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
    GROUP BY 
        usr_id, DATE(login_time)
),
ranked_logins AS (
    SELECT 
        usr_id,
        login_date,
        ROW_NUMBER() OVER (PARTITION BY usr_id ORDER BY login_date) AS row_num
    FROM 
        user_login_days
),
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 ;
2026-03-28 连续登录3天及以上  
with a as(
select 
usr_id,
date(login_time) as login_date
from 
user_login_log 
where
login_time >= date_sub(now(),interval 30 day)
group by
usr_id,
login_date
),
b as(
select
usr_id,
login_date,
row_number()over(partition by usr_id order by login_date) asrow_num
from
a 
),
c as(
select
usr_id,
login_date,
login_date - interval row_num day as grp
from
b 
)
select
usr_id,
 MIN(login_date) AS start_date,
 MAX(login_date) AS end_date,
 COUNT(*) AS consecutive_days
from
c 
group by
usr_id, 
grp
having
COUNT(*) > 2
2026-03-28 抖音面试真题（5）新用户的T+1月留存  
with a 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'
order by
usr_id,
login_month
),
b as(
select
usr_id, 
min(login_month) as first_login_month
from
a 
group by
usr_id
),
c as(
select
a1.usr_id,
a1.first_login_month AS current_month, 
a2.login_month AS next_month
from
b a1
left join
a a2
on
a1.usr_id = a2.usr_id
and
a2.login_month = date_add(a1.first_login_month,interval 1 month)
)
select
current_month,
round(count(distinct case when next_month is not null then usr_id end)*100/count(distinct usr_id),2) as t_plus_1_month_retention_rate
from
c
group by
current_month
order by
1
2026-03-28 抖音面试真题（4）T+1月留存  
with a as(
select 
usr_id,
date_format(login_time,'%Y-%m-01') as login_month
from 
user_login_log 
where
login_time between '2024-01-01' and '2024-11-30'
group by
usr_id,
login_month
),
b as(
select
a1.usr_id,
a1.login_month AS current_month,
a2.login_month AS next_month
from
a a1
left join
a a2
on
a1.usr_id = a2.usr_id
and
a2.login_month = date_add(a1.login_month,interval 1 month)
)
select
current_month,
round(count(distinct case when next_month is not null then usr_id end)*100/count(distinct usr_id),2) as t_plus_1_month_retention_rate
from
b
group by
current_month
order by
1
2026-03-28 抖音面试真题（4）T+1月留存  
with a as(
select 
usr_id,
date_format(login_time,'%Y-%m-01') as login_month
from 
user_login_log 
where
login_time between '2024-01-01' and '2024-12-31'
group by
usr_id,
login_month
),
b as(
select
a1.usr_id,
a1.login_month AS current_month,
a2.login_month AS next_month
from
a a1
left join
a a2
on
a1.usr_id = a2.usr_id
and
a2.login_month = date_add(a1.login_month,interval 1 month)
)
select
current_month,
round(count(distinct case when next_month is not null then usr_id end)*100/count(distinct usr_id),2) as t_plus_1_month_retention_rate
from
b
group by
current_month
order by
1
2026-03-28 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  
with a as(
select 
usr_id,
date(login_time) as login_date
from 
user_login_log 
where
login_time >= date_sub(curdate(),interval 90 day)
group by
usr_id,
login_date
),
b as(
select
a1.usr_id,
a1.login_date AS first_login_date,
 a2.login_date AS next_day_login_date,
datediff(a2.login_date,a1.login_date) as days_diff
from
a a1
left join
a a2
on
a1.usr_id = a2.usr_id
and
a2.login_date between a1.login_date + interval 1 day and a1.login_date + interval 14 day
)
select
first_login_date,
round(count(distinct case when days_diff between 1 and 3 then usr_id end) * 100/count(distinct usr_id),2) as t_plus_1_retention_rate,
round(count(distinct case when days_diff between 1 and 7 then usr_id end) * 100/count(distinct usr_id),2) as t_plus_7_retention_rate, 
round(count(distinct case when days_diff between 1 and 14 then usr_id end) * 100/count(distinct usr_id),2) as t_plus_14_retention_rate
from
b
group by
first_login_date
order by
1
2026-03-28 抖音面试真题（2）一次性计算T+1、T+3、T+7、T+14留存率  
with a as(
select 
usr_id,
date(login_time) as login_date
from 
user_login_log 
where
login_time >= date_sub(CURDATE(),interval 30 day)
group by
usr_id,
login_date
),
b as(
select
a1.usr_id,
a1.login_date AS first_login_date,
 a2.login_date AS next_day_login_date,
datediff(a2.login_date,a1.login_date) as days_diff
from
a a1
left join
a a2
on
a1.usr_id = a2.usr_id
and
a2.login_date between a1.login_date + interval 1 day and a1.login_date + interval 14 day
)
select
first_login_date,
round(count(distinct case when days_diff = 1 then usr_id end) * 100/count(distinct usr_id),2) as t_plus_1_retention_rate,
round(count(distinct case when days_diff = 3 then usr_id end) * 100/count(distinct usr_id),2) as t_plus_3_retention_rate,
round(count(distinct case when days_diff = 7 then usr_id end) * 100/count(distinct usr_id),2) as t_plus_7_retention_rate, 
round(count(distinct case when days_diff = 14 then usr_id end) * 100/count(distinct usr_id),2) as t_plus_14_retention_rate
from
b
group by
first_login_date
order by
1
2026-03-28 抖音面试真题（2）一次性计算T+1、T+3、T+7、T+14留存率  
with a as(
select 
usr_id,
date(login_time) as login_date
from 
user_login_log 
where
login_time >= date_sub(now(),interval 30 day)
group by
usr_id,
login_date
),
b as(
select
a1.usr_id,
a1.login_date AS first_login_date,
 a2.login_date AS next_day_login_date,
datediff(a2.login_date,a1.login_date) as days_diff
from
a a1
left join
a a2
on
a1.usr_id = a2.usr_id
and
a2.login_date between a1.login_date + interval 1 day and a1.login_date + interval 14 day
)
select
first_login_date,
round(count(distinct case when days_diff = 1 then usr_id end) * 100/count(distinct usr_id),2) as t_plus_1_retention_rate,
round(count(distinct case when days_diff = 3 then usr_id end) * 100/count(distinct usr_id),2) as t_plus_3_retention_rate,
round(count(distinct case when days_diff = 7 then usr_id end) * 100/count(distinct usr_id),2) as t_plus_7_retention_rate, 
round(count(distinct case when days_diff = 14 then usr_id end) * 100/count(distinct usr_id),2) as t_plus_14_retention_rate
from
b
group by
first_login_date
order by
1
2026-03-26 抖音面试真题（4）T+1月留存  
with a 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')
),
b as(
select
a1.usr_id,
 a1.login_month AS current_month,
 a2.login_month AS next_month
from
a a1
left join
a a2
on
a1.usr_id = a2.usr_id
and
a2.login_month = a1.login_month + interval 1 month
)
select
current_month,
round(count(distinct case when next_month is not null then usr_id end)*100/count(distinct usr_id),2) as t_plus_1_month_retention_rate
from
b
where
current_month >= '2024-01-01' AND current_month < '2024-12-01'
group by
current_month
order by
1
2026-03-26 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  
WITH daily_unique_logins AS (
    SELECT
        usr_id,
        DATE(login_time) AS login_date
    FROM
        user_login_log
    WHERE
        login_time >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    GROUP BY
        usr_id,
        DATE(login_time)
),
retention_days AS (
    SELECT
        dul1.usr_id,
        dul1.login_date AS first_login_date,
        dul2.login_date AS next_day_login_date,
        DATEDIFF(dul2.login_date, dul1.login_date) AS days_diff
    FROM
        daily_unique_logins dul1
    LEFT JOIN
        daily_unique_logins dul2
    ON
        dul1.usr_id = dul2.usr_id AND
        dul2.login_date BETWEEN dul1.login_date + INTERVAL 1 DAY AND dul1.login_date + INTERVAL 14 DAY
)
SELECT
    first_login_date,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff between 1 and 3 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_3_retention_rate,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff between 1 and 7 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_7_retention_rate,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff between 1 and 14 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_14_retention_rate
FROM
    retention_days
WHERE
    first_login_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY
    first_login_date
ORDER BY
    first_login_date;
2026-03-26 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  
WITH daily_unique_logins AS (
    SELECT
        usr_id,
        DATE(login_time) AS login_date
    FROM
        user_login_log
    WHERE
        login_time >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    GROUP BY
        usr_id,
        DATE(login_time)
),
retention_days AS (
    SELECT
        dul1.usr_id,
        dul1.login_date AS first_login_date,
        dul2.login_date AS next_day_login_date,
        DATEDIFF(dul2.login_date, dul1.login_date) AS days_diff
    FROM
        daily_unique_logins dul1
    LEFT JOIN
        daily_unique_logins dul2
    ON
        dul1.usr_id = dul2.usr_id AND
        dul2.login_date BETWEEN dul1.login_date + INTERVAL 1 DAY AND dul1.login_date + INTERVAL 14 DAY
)
SELECT
    first_login_date,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff = 3 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_3_retention_rate,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff = 7 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_7_retention_rate,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff = 14 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_14_retention_rate
FROM
    retention_days
WHERE
    first_login_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY
    first_login_date
ORDER BY
    first_login_date;
2026-03-26 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  
WITH daily_unique_logins AS (
    SELECT
        usr_id,
        DATE(login_time) AS login_date
    FROM
        user_login_log
    WHERE
        login_time >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
    GROUP BY
        usr_id,
        DATE(login_time)
),
retention_days AS (
    SELECT
        dul1.usr_id,
        dul1.login_date AS first_login_date,
        dul2.login_date AS next_day_login_date,
        DATEDIFF(dul2.login_date, dul1.login_date) AS days_diff
    FROM
        daily_unique_logins dul1
    LEFT JOIN
        daily_unique_logins dul2
    ON
        dul1.usr_id = dul2.usr_id AND
        dul2.login_date BETWEEN dul1.login_date + INTERVAL 3 DAY AND dul1.login_date + INTERVAL 14 DAY
)
SELECT
    first_login_date,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff = 3 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_3_retention_rate,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff = 7 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_7_retention_rate,
    ROUND(COUNT(DISTINCT CASE WHEN days_diff = 14 THEN usr_id END) * 100.0 / COUNT(DISTINCT usr_id), 2) AS t_plus_14_retention_rate
FROM
    retention_days
WHERE
    first_login_date >= DATE_SUB(CURDATE(), INTERVAL 90 DAY)
GROUP BY
    first_login_date
ORDER BY
    first_login_date;