排名

用户解题统计

过去一年提交了

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

收藏

收藏日期 题目名称 解决状态
2025-01-09 窗口函数（2）所有前一和每类前一，继续搞懂排序窗口函数  已解决

评论笔记

评论日期 题目名称 评论内容 站长评论
2025-02-19 抖音面试真题（4）T+1月留存  
答案应该要包括12月
 
啥也没说
2025-01-09 一线城市历年平均气温  
我知道我的问题出现在哪里了。
我一直以为 format( col ,2 ) 和 cast( col as decimal(4,2 ) ) 的结果是完全一样的，其实不对。
问题出在 2013 年 shenzhen 原本的值是 23.125 分别用这两个公式一个 是 23.12 一个是 23.13.后面探究了一下

SELECT cast('23.125' as decimal(4,2 )); -- 23.13
SELECT cast(23.125 as decimal(4,2 ));  -- 23.13
SELECT  format('23.125',2); -- 23.12
SELECT  format(23.125,2); -- 23.13

我原本的脚本在传递给 FORMAT 函数的是一个字符串而不是数字。MySQL 首先需要将这个字符串转换成一个数值类型来处理这种隐式转换在某些环境或者并不可靠。

而且我发现一个 有意思的事情 format('23.125',2)：
当倒数第2位是奇数 比如 23.115，23.135，23.155 ... 结果会四舍五入 即 23.12，23.14，23.16
当倒数第2位是偶数 比如 23.125，23.145，23.165 ... 结果不会四舍五入 也是 23.12，23.14，23.16

估计是因为隐式转换的原因，具体的我没有搞明白，希望有看到这里的大佬可以解惑一下！
 
你的研究很仔细哈哈哈。舍入目的还是建议使用round，mysql一直有点疯，不太稳定。
2025-01-09 一线城市历年平均气温  
输出的是和输出示例是一样的呀，问题在哪？
select 
	year(dt) as Y
	, format(avg(REGEXP_SUBSTR(if(city = 'beijing', tmp_h, null), '-?[0-9]+')), 2)as beijing
	, format(avg(REGEXP_SUBSTR(if(city = 'shanghai', tmp_h, null), '-?[0-9]+')), 2)as shanghai
	, format(avg(REGEXP_SUBSTR(if(city = 'shenzhen', tmp_h, null), '-?[0-9]+')), 2)as shenzhen
	, format(avg(REGEXP_SUBSTR(if(city = 'guangzhou', tmp_h, null), '-?[0-9]+')), 2)as guangzhou
from weather_rcd_china
where 
	year(dt) between 2011 and 2022
	and city in ('beijing','shanghai','shenzhen','guangzhou')
group by 1
order by 1
 
啥也没说
2025-01-09 城市平均最高气温  
ROUND() 函数本身不会保证总是显示两位小数
可以使用 FORMAT() 函数或 DECIMAL 数据类型来控制输出格式
 
啥也没说
2025-01-09 滴滴面试真题（3）UTC转化后的本地时间呼叫高峰期  
提交不对呢，参考答案提交也不对
 
参考答案当然不对咯，毕竟只能参考嘛 巴西跟UTC时间到底差了多少呢？
2025-01-09 快手面试真题（3）同时在线人数峰值时点  
首先输出实例和提议不符，我做的结果和参考答案是一样的，提交是错误，参考答案提交也是错误
 
已修改，可以再试试
2025-01-09 快手面试真题（2）同时在线人数峰值  
记录一下第一次算峰值，想了很久，不知道还有没有其他的简单方法
with data1 as(
	select usr_id, live_id, enter_time as time1, 1 as flag1
	from ks_live_t1
	union all
	select usr_id, live_id, leave_time as time1, -1 as flag1
	from ks_live_t1
), 
data2 as(
	select 
		live_id
		, sum(flag1) over(partition by live_id order by time1 ) as online_users
	from data1
)
select t1.live_id,t2.live_nm, max(online_users) as max_online_users
from data2 t1 left join ks_live_t2 t2 on t1.live_id = t2.live_id
group by 1, 2
order by 3 desc
 
啥也没说
2025-01-09 10月1日后再也没活跃过的用户  
如果活跃的定义就是登录的话，那么只需要判断最后一次登录时间是否 <=2024-10-01 不就可以吗
select count(1) as inactive_user_count
from(
	select usr_id, max(login_time)
	from user_login_log
	group by 1
	having max(login_time) <= '2024-10-01'
) tmp
 
同样妙不可言
2025-01-09 窗口函数（4）越来越喜欢召妓，窗口函数用累计（2）  
答案只包含2023的记录，没有2024，建议修改题目或答案
 
啥也没说
2025-01-09 窗口函数（2）所有前一和每类前一，继续搞懂排序窗口函数  
需求描述是：消费笔数Top1 题目又是前二，相互间不一致，建议统一
 
已改，笔芯！
2025-01-08 时间日期（4）阶段综合-按月统计日花费，一天都不要浪费  
输出示例表格的第一个字段 rx_mon 前面少了字母t 容易误导
 
已调整答案校验逻辑，不再care 字段名称。
2025-01-08 小结（1）大数据早就能扫黄，找足证据不慌张  
问一下 区间限定[23:00:00, 03:00:00) 为什么答案 between 0 and 3 不是 between 0 and 2 呀？
 
啥也没说
2025-01-08 小结（1）大数据早就能扫黄，找足证据不慌张  
题目给定时间区间[23:00:00, 03:00:00) 为什么要 between 0 and 3 不应该  between 0 and 2 吗
 
已修改～

提交记录

提交日期 题目名称 提交代码
2025-06-18 抖音面试真题（5）新用户的T+1月留存  
with tmp as(
	select 
		usr_id,
		date_format(login_time,'%Y-%m-01') dt, 
		date_format(min(login_time)over(partition by usr_id),'%Y-%m-01') reg_dt
	from user_login_log
	wherelogin_time >= '2024-01-01' AND login_time < '2025-01-01'
), 
tmp2 as(
	select reg_dt, count(distinct usr_id) total_user
	from tmp
	group by 1
), 
rete as(
select
	reg_dt, 
	sum(case when min_gap = 1 then 1 else 0 end) rete
from(
	select
		usr_id, 
		reg_dt, 
		min(timestampdiff(month, reg_dt, dt)) min_gap
	from tmp 
	where timestampdiff(month, reg_dt, dt) <> 0
	group by 1, 2
	) t1
group by1
)
select 
	a.reg_dt current_month, 
	round(ifnull(b.rete / a.total_user *100, 0), 2) t_plus_1_month_retention_rate
from tmp2 a left join rete b on a.reg_dt = b.reg_dt
2025-06-18 抖音面试真题（5）新用户的T+1月留存  
with tmp1 as(
	select 
		usr_id,
		date_format(min(login_time),'%Y-%m-01') reg_dt
	from user_login_log
	where login_time >= '2024-01-01' AND login_time < '2025-01-01'
	group by 1
),
tmp2 as(
	select 
		usr_id,
		date_format(login_time,'%Y-%m-01') dt
	from user_login_log
	where login_time >= '2024-01-01' AND login_time < '2025-01-01'
	group by 1,2
)
select
	a.reg_dt current_month	,
	round(count(b.dt)*100 / count(a.reg_dt),2) t_plus_1_month_retention_rate
from tmp1 a left join tmp2 b 
	on a.usr_id = b.usr_id 
	and date(a.reg_dt) = date_sub(b.dt,interval 1 month)
group by 1
order by 1
2025-06-18 抖音面试真题（5）新用户的T+1月留存  
with tmp as(
	select 
		usr_id,
		date_format(login_time,'%Y-%m-01') dt, 
		date_format(min(login_time)over(partition by usr_id),'%Y-%m-01') reg_dt
	from user_login_log
	wherelogin_time >= '2024-01-01' AND login_time < '2025-01-01'
)
select
	a.reg_dt current_month	,
	round(count(distinct b.usr_id)*100 / count(distinct a.usr_id),2) t_plus_1_month_retention_rate
from tmp a left join tmp b 
	on a.usr_id = b.usr_id 
	and date(a.reg_dt) = date_sub(b.dt,interval 1 month)
group by 1
order by 1
2025-06-18 抖音面试真题（5）新用户的T+1月留存  
with tmp as(
	select 
		usr_id,
		date_format(login_time,'%Y-%m-01') dt, 
		date_format(min(login_time)over(partition by usr_id),'%Y-%m-01') reg_dt
	from user_login_log
	wherelogin_time >= '2024-01-01' AND login_time < '2025-02-01'
)
select
	a.reg_dt current_month	,
	round(count(distinct b.usr_id)*100 / count(distinct a.usr_id),2) t_plus_1_month_retention_rate
from tmp a left join tmp b 
	on a.usr_id = b.usr_id 
	and date(a.reg_dt) = date_sub(b.dt,interval 1 month)
group by 1
order by 1
2025-06-18 抖音面试真题（5）新用户的T+1月留存  
with tmp1 as(
	select 
		usr_id,
		date_format(min(login_time),'%Y-%m-01') reg_dt
	from user_login_log
	where login_time >= '2024-01-01' AND login_time < '2025-01-01'
	group by 1
),
tmp2 as(
	select 
		usr_id,
		date_format(login_time,'%Y-%m-01') dt
	from user_login_log
	where login_time >= '2024-01-01' AND login_time < '2025-01-01'
	group by 1,2
)
select
	a.reg_dt current_month	,
	round(count(b.dt)*100 / count(a.reg_dt),2) t_plus_1_month_retention_rate
from tmp1 a left join tmp2 b 
	on a.usr_id = b.usr_id 
	and DATE_FORMAT(DATE_ADD(a.reg_dt, INTERVAL 1 MONTH), '%Y-%m-01') = b.dt
group by 1
order by 1
2025-06-18 抖音面试真题（4）T+1月留存  
with tmp as(
	select 
		usr_id,
		date_format(login_time,'%Y-%m-01') dt
	from user_login_log
	wherelogin_time >= '2024-01-01' AND login_time < '2025-02-01'
	group by 1,2
)
select
	a.dt current_month	,
	round(count(b.dt)*100 / count(a.dt),2) t_plus_1_month_retention_rate
from tmp a left join tmp b on a.usr_id = b.usr_id and date(a.dt) = date_sub(b.dt,interval 1 month) 
where a.dt < '2024-12-01'
group by 1
order by 1
2025-06-18 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  
with tmp1 as(
	select usr_id,date(login_time) dt
	from user_login_log
	where datediff(current_date(), date(login_time)) <= 90
	group by 1,2
)
select 
	a.dt first_login_date,
	round(count(distinct case when datediff(b.dt,a.dt) BETWEEN 1 AND 3then a.usr_id end ) *100/ count(distinct a.usr_id) ,2) t_plus_3_retention_rate,
	round(count(distinct case when datediff(b.dt,a.dt) BETWEEN 1 AND 7then a.usr_id end ) *100/ count(distinct a.usr_id) ,2) t_plus_7_retention_rate,
	round(count(distinct case when datediff(b.dt,a.dt) BETWEEN 1 AND 14 then a.usr_id end ) *100 /count(distinct a.usr_id) ,2) t_plus_14_retention_rate
from tmp1 a 
	left join tmp1 b 
	on a.usr_id = b.usr_id 
	and datediff(b.dt,a.dt) BETWEEN 1 AND 14
group by 1
order by 1
2025-06-18 抖音面试真题（1）T+1日留存率  
with tmp as(
	select usr_id, date(login_time) dt
	from user_login_log
	where datediff(current_date(), date(login_time)) <=30
	group by 1, 2
)
select 
	a.dt login_date, 
	concat(round(count(distinct b.usr_id) / count(distinct a.usr_id)*100, 2), '%') T1_retention_rate
from 
	tmp a 
	left join tmp b 
	on a.usr_id = b.usr_id 
	and datediff(b.dt, a.dt) = 1
group by 1
order by 1
2025-06-18 抖音面试真题（1）T+1日留存率  
with tmp as(
	select usr_id, date(login_time) dt
	from user_login_log
	group by 1, 2
)
select 
	a.dt login_date, 
	concat(round(count(distinct b.usr_id) / count(distinct a.usr_id)*100, 2), '%') T1_retention_rate
from 
	tmp a 
	left join tmp b 
	on a.usr_id = b.usr_id 
	and datediff(b.dt, a.dt) = 1
where datediff(current_date(), a.dt) <=30
group by 1
order by 1
2025-06-18 抖音面试真题（1）T+1日留存率  
with tmp as(
	select usr_id, date(login_time) dt
	from user_login_log
	group by 1, 2
)
select 
	a.dt login_date, 
	concat(round(avg(b.dt is not null)*100, 2), '%') T1_retention_rate
from 
	tmp a 
	left join tmp b 
	on a.usr_id = b.usr_id 
	and datediff(b.dt, a.dt) = 1
where datediff(current_date(), a.dt) <=30
group by 1
order by 1
2025-06-18 抖音面试真题（1）T+1日留存率  
with tmp as(
	select usr_id, date(login_time) dt
	from user_login_log
	group by 1, 2
)
select 
	a.dt login_date, 
	concat(round(count(b.dt) *100/count(a.dt), 2), '%') T1_retention_rate
from 
	tmp a 
	left join tmp b 
	on a.usr_id = b.usr_id 
	and datediff(b.dt, a.dt) = 1
where datediff(current_date(), a.dt) <=30
group by 1
order by 1
2025-06-18 抖音面试真题（1）T+1日留存率  
with tmp as(
	select usr_id, date(login_time) dt
	from user_login_log
	group by 1, 2
)
select 
	a.dt login_date, 
	concat(round(count(b.dt) *100/count(a.dt), 2), '%') T1_retention_rate
from 
	tmp a 
	left join tmp b 
	on a.usr_id = b.usr_id 
	and a.dt = date_sub(b.dt, interval 1 day)
where datediff(current_date(), a.dt) <=30
group by 1
order by 1
2025-06-18 5月3日的所有打车记录  
select *
from didi_order_rcd
where 
	date(call_time) = '2021-05-03'
and cancel_time <> '1970-01-01'
2025-06-18 每个视频类型的T+3留存率  
with tmp1 as(
	select
		a.usr_id, 
		a.v_id, 
		b.v_typ, 
		date(a.v_tm) v_dt
	from bilibili_t20 a left join bilibili_t3 b on a.v_id = b.v_id
	where date(a.v_tm) between '2021-02-05' and '2021-02-08'
)
select
	a.v_typ, 
	count(distinct a.usr_id) total_views, 
	count(distinct b.usr_id) retained_users, 
	round(count(distinct b.usr_id) / count(distinct a.usr_id) *100, 2) retention_rate
from tmp1 a left join tmp1 b on a.usr_id = b.usr_idand datediff(b.v_dt, a.v_dt) between 1 and 3
where a.v_dt = '2021-02-05'
group by 1
order by 4 desc
2025-06-18 每个视频类型的T+3留存率  
with tmp1 as(
	select
		a.usr_id, 
		a.v_id, 
		b.v_typ, 
		date(a.v_tm) v_dt
	from bilibili_t20 a left join bilibili_t3 b on a.v_id = b.v_id
	where a.v_tm between '2021-02-05' and '2021-02-09'
)
select
	a.v_typ, 
	count(distinct a.usr_id) total_views, 
	count(distinct b.usr_id) retained_users, 
	round(count(distinct b.usr_id) / count(distinct a.usr_id) *100, 2) retention_rate
from tmp1 a left join tmp1 b on a.usr_id = b.usr_idand datediff(b.v_dt, a.v_dt) between 1 and 3
where a.v_dt = '2021-02-05'
group by 1
order by 4 desc
2025-06-17 抖音面试真题（5）新用户的T+1月留存  
with tmp1 as(
	select 
		usr_id,
		date_format(min(login_time),'%Y-%m-01') reg_dt
	from user_login_log
	where login_time >= '2024-01-01' AND login_time < '2025-01-01'
	group by 1
),
tmp2 as(
	select 
		usr_id,
		date_format(login_time,'%Y-%m-01') dt
	from user_login_log
	where login_time >= '2024-01-01' AND login_time < '2025-01-01'
	group by 1,2
)
select
	a.reg_dt current_month	,
	round(count(b.dt)*100 / count(a.reg_dt),2) t_plus_1_month_retention_rate
from tmp1 a left join tmp2 b 
	on a.usr_id = b.usr_id 
	and DATE_FORMAT(DATE_ADD(a.reg_dt, INTERVAL 1 MONTH), '%Y-%m-01') = b.dt
where a.reg_dt < '2024-12-01'
group by 1
order by 1
2025-06-17 抖音面试真题（4）T+1月留存  
with tmp as(
	select 
		usr_id,
		date_format(login_time,'%Y-%m-01') dt
	from user_login_log
	wherelogin_time >= '2024-01-01' AND login_time < '2025-01-01'
	group by 1,2
)
select
	a.dt current_month	,
	round(count(b.dt)*100 / count(a.dt),2) t_plus_1_month_retention_rate
from tmp a left join tmp b on a.usr_id = b.usr_id and date(a.dt) = date_sub(b.dt,interval 1 month) 
where a.dt	< '2024-12-01'
group by 1
order by 1
2025-06-17 抖音面试真题（2）一次性计算T+1、T+3、T+7、T+14留存率  
with tmp1 as(
	select usr_id,date(login_time) dt
	from user_login_log
	where date(login_time) >= date_sub(current_date(),interval 30 day)
	group by 1,2
)
select 
	a.dt first_login_date,
	round(count(distinct case when datediff(b.dt,a.dt) = 1then a.usr_id end ) *100 / count(distinct a.usr_id) ,2) t_plus_1_retention_rate,
	round(count(distinct case when datediff(b.dt,a.dt) = 3then a.usr_id end ) *100 / count(distinct a.usr_id) ,2) t_plus_3_retention_rate,
	round(count(distinct case when datediff(b.dt,a.dt) = 7then a.usr_id end ) *100 / count(distinct a.usr_id) ,2) t_plus_7_retention_rate,
	round(count(distinct case when datediff(b.dt,a.dt) = 14 then a.usr_id end ) *100 / count(distinct a.usr_id),2) t_plus_14_retention_rate
from tmp1 a 
	left join tmp1 b 
	on a.usr_id = b.usr_id 
	and datediff(b.dt,a.dt) BETWEEN 1 AND 14
group by 1
order by 1
2025-06-17 抖音面试真题（2）一次性计算T+1、T+3、T+7、T+14留存率  
with tmp1 as(
	select usr_id,date(login_time) dt
	from user_login_log
	where date(login_time) >= date_sub(current_date(),interval 30 day)
	group by 1,2
)
select 
	a.dt first_login_date,
	round(count(distinct case when datediff(b.dt,a.dt) = 1 then 1 else 0 end ) *100/ count(distinct a.usr_id) ,2) t_plus_1_retention_rate,
	round(count(distinct case when datediff(b.dt,a.dt) = 3 then 1 else 0 end ) *100/ count(distinct a.usr_id) ,2) t_plus_3_retention_rate,
	round(count(distinct case when datediff(b.dt,a.dt) = 7 then 1 else 0 end ) *100/ count(distinct a.usr_id) ,2) t_plus_7_retention_rate,
	round(count(distinct case when datediff(b.dt,a.dt) = 14 then 1 else 0 end )*100 /count(distinct a.usr_id),2) t_plus_14_retention_rate
from tmp1 a 
	left join tmp1 b 
	on a.usr_id = b.usr_id 
	and datediff(b.dt,a.dt) BETWEEN 1 AND 14
group by 1
order by 1
2025-06-17 抖音面试真题（3）一次性计算T+3、T+7、T+14留存率（2）  
with tmp1 as(
	select usr_id,date(login_time) dt
	from user_login_log
	where date(login_time) >= date_sub(current_date(),interval 90 day)
	group by 1,2
)
select 
	a.dt first_login_date,
	round(count(distinct case when datediff(b.dt,a.dt) BETWEEN 1 AND 3then a.usr_id end ) *100/ count(distinct a.usr_id) ,2) t_plus_3_retention_rate,
	round(count(distinct case when datediff(b.dt,a.dt) BETWEEN 1 AND 7then a.usr_id end ) *100/ count(distinct a.usr_id) ,2) t_plus_7_retention_rate,
	round(count(distinct case when datediff(b.dt,a.dt) BETWEEN 1 AND 14 then a.usr_id end )*100 /count(distinct a.usr_id),2) t_plus_14_retention_rate
from tmp1 a 
	left join tmp1 b 
	on a.usr_id = b.usr_id 
	and datediff(b.dt,a.dt) BETWEEN 1 AND 14
group by 1
order by 1