排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2026-02-04 快手面试真题（2）同时在线人数峰值  已解决

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2026-03-12 抖音面试真题（4）T+1月留存  
WITH UserMonthly AS (
SELECT DISTINCT 
usr_id, 
DATE_FORMAT(login_time, '%Y-%m') AS login_month
FROM user_login_log
WHERE login_time >= '2024-01-01' AND login_time < '2025-02-01'
)
SELECT 
t1.login_month AS current_month,
ROUND(COUNT(DISTINCT t2.usr_id) * 100.0 / COUNT(DISTINCT t1.usr_id), 2) AS monthly_retention_rate
FROM 
UserMonthly t1
LEFT JOIN 
UserMonthly t2 ON t1.usr_id = t2.usr_id 
AND t2.login_month = DATE_FORMAT(DATE_ADD(CONCAT(t1.login_month, '-01'), INTERVAL 1 MONTH), '%Y-%m')
WHERE 
t1.login_month LIKE '2024%' 
GROUP BY 
t1.login_month
ORDER BY 
current_month;
2026-03-12 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  
with data as(
select
	distinct usr_id ,
date(login_time) as login_date
from user_login_log
where datediff(current_date,date(login_time))<=90
)
select
t1.login_date as first_login_date,
 round(count(distinct case when datediff(t2.login_date,t1.login_date) between 1 and 3 then t2.usr_id end ) /count(distinct t1.usr_id)*100,2) as t_plus_3_retention_rate,
round(count(distinct case when datediff(t2.login_date,t1.login_date) between 1 and 7 then t2.usr_id end ) /count(distinct t1.usr_id)*100,2) as t_plus_7_retention_rate,
round(count(distinct case when datediff(t2.login_date,t1.login_date) between 1 and 14 then t2.usr_id end) /count(distinct t1.usr_id)*100,2) as t_plus_14_retention_rate
from data t1 
left join data t2
on t1.usr_id=t2.usr_id and t1.login_date <t2.login_date
group by first_login_date
order by first_login_date
2026-03-12 抖音面试真题（2）一次性计算T+1、T+3、T+7、T+14留存率  
with shoushen as(
select 
distinct usr_id ,
date(login_time) as login_date
from user_login_log 
where datediff(current_date,date(login_time))<=30
)
select
	t1.login_date as first_login_date,
	ROUND(
COUNT(DISTINCT CASE WHEN DATEDIFF(t2.login_date, t1.login_date) = 1 THEN t2.usr_id END) * 100.0 / COUNT(DISTINCT t1.usr_id), 2) AS
 t_plus_1_retention_rate,
ROUND(
COUNT(DISTINCT CASE WHEN DATEDIFF(t2.login_date, t1.login_date) = 3 THEN t2.usr_id END) * 100.0 / COUNT(DISTINCT t1.usr_id), 2) AS
 t_plus_3_retention_rate,
ROUND(
COUNT(DISTINCT CASE WHEN DATEDIFF(t2.login_date, t1.login_date) = 7 THEN t2.usr_id END) * 100.0 / COUNT(DISTINCT t1.usr_id), 2) AS
 t_plus_7_retention_rate,
ROUND(
COUNT(DISTINCT CASE WHEN DATEDIFF(t2.login_date, t1.login_date) = 14 THEN t2.usr_id END) * 100.0 / COUNT(DISTINCT t1.usr_id), 2) AS
 t_plus_14_retention_rate
from shoushen t1
left join shoushen t2 
on t1.usr_id=t2.usr_id and t2.login_date >t1.login_date
group by first_login_date
order by first_login_date
2026-03-12 抖音面试真题（1）T+1日留存率  
SELECT 
t1.login_date,
CONCAT(ROUND(COUNT(DISTINCT t2.usr_id) * 100.0 / COUNT(DISTINCT t1.usr_id), 2), "%") AS T1_retention_rate
FROM 
(
SELECT DISTINCT usr_id, DATE(login_time) AS login_date
FROM user_login_log 
WHERE DATE(login_time) >= DATE_SUB(CURDATE(), INTERVAL 30 DAY) 
) t1
LEFT JOIN 
(
SELECT DISTINCT usr_id, DATE(login_time) AS login_date
FROM user_login_log
) t2 
ON t1.usr_id = t2.usr_id 
 AND t2.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY)
GROUP BY 
t1.login_date
ORDER BY 
t1.login_date ASC;
2026-03-12 抖音面试真题（1）T+1日留存率  
with data1 as (
    select distinct 
        usr_id,
        date(login_time) as login_date 
    from 
        user_login_log 
    where 
        datediff(current_date, date(login_time)) <= 30
),
data2 as (
    select 
        T.usr_id, 
        T.login_date as T_date, 
        T_1.login_date as T_1_date 
    from 
        data1 as T 
    left join 
        data1 as T_1 
    on 
        T.usr_id = T_1.usr_id 
        and datediff(T.login_date, T_1.login_date) = -1
)
select 
    T_date as first_login_date, 
    concat(round(avg(T_1_date is not null)*100, 2), '%') as T1_retention_rate 
from 
    data2 
group by 
    T_date 
order by 
    T_date;
2026-03-12 抖音面试真题（1）T+1日留存率  
WITH user_login AS (
SELECT
usr_id,
DATE(login_time) AS login_date
FROM
user_login_log
WHERE
login_time >= CURDATE() - INTERVAL 30 DAY
GROUP BY
usr_id, login_date
),
t_day_users AS (
SELECT
login_date AS t_date,
COUNT(DISTINCT usr_id) AS A
FROM
user_login
GROUP BY
login_date
),
t_plus_one_users AS (
SELECT
ul1.login_date AS t_date,
COUNT(DISTINCT ul2.usr_id) AS B
FROM
user_login ul1
LEFT JOIN
user_login ul2
ON
ul1.usr_id = ul2.usr_id
AND ul1.login_date = DATE_SUB(ul2.login_date, INTERVAL 1 DAY)
GROUP BY
ul1.login_date
)
SELECT
t1.t_date AS login_date,
ROUND(IFNULL(t2.B / t1.A * 100, 0), 2) AS T1_retention_rate
FROM
t_day_users t1
LEFT JOIN
t_plus_one_users t2
ON
t1.t_date = t2.t_date
ORDER BY
t1.t_date DESC;
2026-03-10 抖音面试真题（1）T+1日留存率  
select
	a.login_date,
concat(round(count(b.usr_id)/count(a.usr_id),2)*100,'%')as T1_retention_rate
from
(select 
	distinct usr_id,
date(login_time) as login_date
from user_login_log )a
left join 
	(select distinct usr_id,
date(login_time) as login_date
from user_login_log) b 
ona.usr_id=b.usr_id
and b.login_date=date_add(a.login_date ,interval 1 day)
where a.login_date >= curdate() - interval 30 day 
	and a.login_date<= curdate()
group by a.login_date
order by a.login_date
2026-03-10 抖音面试真题（1）T+1日留存率  
select
	a.login_date,
concat(round(count(b.usr_id)/count(a.usr_id),2)*100,'%')as T1_retention_rate
from
(select 
	distinct usr_id,
date(login_time) as login_date
from user_login_log )a
left join 
	(select distinct usr_id,
date(login_time) as login_date
from user_login_log) b 
ona.usr_id=b.usr_id
and b.login_date=date_add(a.login_date ,interval 1 day)
where a.login_date >= curdate() - interval 30 day 
	and a.login_date< curdate()
group by a.login_date
order by a.login_date
2026-02-13 抖音面试真题（1）T+1日留存率  
select 
	t1.login_date,
concat(round(count(distinct t2.usr_id)*100/count(distinct t1.usr_id),2),"%") as T1_retention_rate
from 
(select distinct usr_id ,date(login_time) as login_date
 from user_login_log 
 where date(login_time)>=date_sub(curdate(),interval 30 day)) t1
 left join 
 (select distinct usr_id,date(login_time) as login_date
 from user_login_log
 ) t2 
 on t1.usr_id=t2.usr_id and t2.login_date=date_add(t1.login_date,interval 1 day)
group by t1.login_date
 order by t1.login_date asc
2026-02-05 抖音面试真题（1）T+1日留存率  
SELECT 
t1.login_date,
CONCAT(
ROUND(COUNT(DISTINCT t2.usr_id) * 100.0 / COUNT(DISTINCT t1.usr_id), 2), 
'%'
) AS T1_retention_rate
FROM 
(
SELECT DISTINCT usr_id, DATE(login_time) AS login_date 
FROM user_login_log
WHERE login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
) t1
LEFT JOIN 
(
SELECT DISTINCT usr_id, DATE(login_time) AS login_date 
FROM user_login_log
) t2 
ON t1.usr_id = t2.usr_id 
 AND t2.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY)
GROUP BY 
t1.login_date
ORDER BY 
t1.login_date ASC;
2026-02-05 抖音面试真题（1）T+1日留存率  
SELECT 
t1.login_date AS T_date,
ROUND(COUNT(DISTINCT t2.usr_id) * 100.0 / COUNT(DISTINCT t1.usr_id), 2) AS next_day_retention_rate
FROM 
(
SELECT DISTINCT usr_id, DATE(login_time) AS login_date 
FROM user_login_log
WHERE login_time >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
) t1
LEFT JOIN 
(
SELECT DISTINCT usr_id, DATE(login_time) AS login_date 
FROM user_login_log
) t2 
ON t1.usr_id = t2.usr_id 
 AND t2.login_date = DATE_ADD(t1.login_date, INTERVAL 1 DAY) 
GROUP BY 
t1.login_date
ORDER BY 
T_date DESC;
2026-02-05 7月之后再也没活跃过的用户  
with qihuoguo as(
select 
	usr_id
from user_login_log
WHERE login_time >= '2024-07-01' AND login_time < '2024-08-01'
group by usr_id 
having COUNT(*) >= 10
),
 qihouhuo as(
 SELECT 
usr_id
FROM
 user_login_log
WHERE login_time >= '2024-08-01'
GROUP BY usr_id, DATE_FORMAT(login_time, '%Y-%m'
)
HAVING COUNT(*) >= 10
 )
SELECT 
COUNT(DISTINCT usr_id) AS
 inactive_user_count
FROM 
qihuoguo
WHERE 
usr_id 
NOT IN (SELECT usr_id FROM
 qihouhuo)
2026-02-05 7月之后再也没活跃过的用户  
select count(distinct usr_id )from
(
select 
	usr_id
from user_login_log
group by usr_id 
having count(case when date_format(login_time,'%Y-%m')="2024-07" then 1 else 0 end)>=10 and max(login_time)<'2024-08-01' ) as f
2026-02-05 10月1日后再也没活跃过的用户  
select
	count(distinct usr_id) as inactive_user_count
from user_login_log
where login_time<='2024-10-01' andusr_id not in 
(selectdistinct usr_id
 from user_login_log 
 where login_time >'2024-10-01')
2026-02-05 10月1日后再也没活跃过的用户  
with first as(
select
	distinct usr_id
from user_login_log
where login_time<='2024-10-01')
select
 count(*) as inactive_user_count
from first
2026-02-05 10月1日后再也没活跃过的用户  
with first as(
select
	distinct usr_id
from user_login_log
where login_time<'2024-10-02')
select
 count(*) as inactive_user_count
from first
2026-02-05 10月1日后再也没活跃过的用户  
with first as(
select
	distinct usr_id
from user_login_log
where login_time<'2024-10-02 00:00')
select
 count(*) as inactive_user_count
from first
2026-02-05 10月1日后再也没活跃过的用户  
with first as(
select
	distinct usr_id
from user_login_log
where login_time>'2024-10-02 00:00')
select
 count(*) as inactive_user_count
from first
2026-02-05 10月1日后再也没活跃过的用户  
with first as(
select
	usr_id
from user_login_log
where login_time>'2024-10-02 00:00')
select
 count(*) as inactive_user_count
from first
2026-02-05 10月1日后再也没活跃过的用户  
with first as(
select
	usr_id
from user_login_log
where login_time>'2024-10-01 23:59')
select
 count(*) as inactive_user_count
from first