排名

用户解题统计

过去一年提交了

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

收藏

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

评论笔记

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

提交记录

提交日期 题目名称 提交代码
2025-01-28 想念她的味道,工资献给98号 
select 
*
from cmb_usr_trx_rcd
where year(trx_time) in(2022,2023,2024)
 andday(trx_time) in(28,29,30,31,1)
 and usr_id=5201314520
 and trx_amt>300
order by trx_time asc;
2025-01-28 想念她的味道,工资献给98号 
select 
*
from cmb_usr_trx_rcd
where year(trx_time) in(2022,2023,2024)
 andday(trx_time) in(28,29,30,31,1)
 and trx_amt>300
order by trx_time asc;
2025-01-28 双脚踏进足浴门,从此再无心上人 
select * from cmb_usr_trx_rcd 
where 
	date(trx_time) between '2024-06-08' and'2024-06-10'
and hour(trx_time) in(11,12,18,19)
and 	mch_nm='红玫瑰按摩保健休闲'
 order by
 	 trx_time asc;
2025-01-28 双脚踏进足浴门,从此再无心上人 
select * from cmb_usr_trx_rcd 
where 
	date(trx_time) between '2024-06-08' and'2024-06-10'
and hour(trx_time) in(11,12,18,19)
 order by
 	 trx_time asc;
2025-01-28 5月3日的所有打车记录 
select
*
from didi_order_rcd
where 
	date(call_time)='2021-05-03'
and date(cancel_time)>'1970-01-01'
order by
	call_time;
2025-01-28 渣男腰子可真行,端午中秋干不停 
select * from cmb_usr_trx_rcd 
where 
	(date(trx_time) between '2024-06-08' and '2024-06-10'
or 	date(trx_time) between '2024-09-15' and '2024-09-17')
and usr_id=5201314520
 order by
 	trx_time asc;
2025-01-28 渣男腰子可真行,端午中秋干不停 
select * from cmb_usr_trx_rcd 
where 
	(date(trx_time) between '2024-06-08' and '2024-06-10'
or 	date(trx_time) between '2024-09-15' and '2024-09-17')
and usr_id=5201314520;
2025-01-28 横屏与竖屏视频的完播率(按AI配音和字幕分类) 
with a as
(select
	uid
,kl.video_id
,timestampdiff(second,start_time,end_time) dt
,duration
,if_AI_talking
,if_hint
,screen_type
,case whenif_AI_talking=1 andif_hint=1then'AI_with_hint'
when if_AI_talking=1 andif_hint=0 then 'AI_no_hint'
when if_AI_talking=0 andif_hint=1 then 'no_AI_with_hint'
when if_AI_talking=0 andif_hint=0 then 'no_AI_no_hint'
end lable
from 
	ks_video_wat_log kl
left join
	ks_video_inf ki
on 
	kl.video_id=ki.video_id
)
select
	screen_type
	,round(count(distinct case whenlable= 'AI_with_hint' anddt>duration then uid else null end)/count(distinct case whenlable= 'AI_with_hint' then uid else null end)*100,2)AI_with_hint
 	,round(count(distinct case whenlable= 'AI_no_hint' anddt>duration then uid else null end)/count(distinct case whenlable= 'AI_no_hint' then uid else null end)*100,2)AI_no_hint
,round(count(distinct case whenlable= 'no_AI_with_hint' anddt>duration then uid else null end)/count(distinct case whenlable= 'no_AI_with_hint' then uid else null end)*100,2)no_AI_with_hint
,round(count(distinct case whenlable= 'no_AI_no_hint' anddt>duration then uid else null end)/count(distinct case whenlable= 'no_AI_no_hint' then uid else null end)*100,2)no_AI_no_hint
from
	a
group by
	screen_type;
2025-01-28 播放量最高的标签 
with p as
(select
*
,1+a.i+b.i1*10 as n
from
(
select 
 0 i 
union 
select 
1
union 
select 
2 
union 
select 
3 
union 
select 
4 
union 
select 
5
union 
select 
6
union 
select 
7
union 
select 
8
union 
select 
9
) as a
,
(
select 
 0 i1 
union 
select 
1
union 
select 
2 
union 
select 
3 
union 
select 
4 
union 
select 
5
union 
select 
6
union 
select 
7
union 
select 
8
union 
select 
9
) as b 
 where 
 1+a.i+b.i1*10<=(
select 
max(length(tag)-length(replace(tag,' ',''))+1)
from
ks_video_inf
 )
),
w as
(
select
video_id
,substring_index(substring_index(tag,' ',p.n),' ',-1) tag 
,substring_index(tag,' ',p.n)
from 
ks_video_inf ks 
left join
p 
on 
length(tag)-length(replace(tag,' ',''))+1>=p.n
)
select
	tag
,count(1)
from 
	ks_video_wat_log lg
left join
	w 
on
	lg.video_id=w.video_id
where 
	date(start_time)>=date_sub(current_date,interval 1 month)
group by
	tag
order by
	count(1) desc
limit 1;
2025-01-28 找出与X轴交点小于等于0的一元一次函数 
select * from numbers_for_fun limit 5;
2025-01-15 专注力强的总用户数 
select
	count(distinct uid) total_users
from
(select
	uid
,count(1)
from 
ks_video_wat_log a 
left join ks_video_inf b
on a.video_id=b.video_id
where 
 date(start_time)>=date_sub(current_date,interval 1 month)
 	and duration>=180
and timestampdiff(second,start_time,end_time)>=duration
group by
	uid
having count(1)>=2
 ) p;
2025-01-15 专注力强的总用户数 
select
	count(distinct uid)
from
(select
	uid
,sum(case whenduration/60>=3 and(timestampdiff(second,start_time,end_time)/60)>=(duration/60) then 1 else 0 end)type
from 
ks_video_wat_log a 
left join ks_video_inf b
on a.video_id=b.video_id
where 
 date(start_time)>=date_sub(current_date,interval 1 month)
group by
	uid
having sum(case whenduration/60>=3 and(timestampdiff(second,start_time,end_time)/60)>=(duration/60) then 1 else 0 end)>=2
 ) p;
2025-01-15 专注力强的总用户数 
select
	count(distinct uid)
from
(select
	uid
,sum(case whenduration/60>3 and(timestampdiff(second,start_time,end_time)/60)>(duration/60) then 1 else 0 end)type
from 
ks_video_wat_log a 
left join ks_video_inf b
on a.video_id=b.video_id
where 
 date(start_time)>=date_sub(current_date,interval 1 month)
group by
	uid
having sum(case whenduration/60>3 and(timestampdiff(second,start_time,end_time)/60)>(duration/60) then 1 else 0 end)>=2
 ) p
2025-01-15 计算完播率(按人数) 
select
a.video_id
,title
,round(count(distinct case whentimestampdiff(second,start_time,end_time)>=duration then uid else null end)/count(distinct uid)*100,4) rate
from 
ks_video_wat_log a 
left join
ks_video_inf b
on 
a.video_id=b.video_id
where 
 date(start_time)>=date_sub(current_date,interval 1 month)
 group by
a.video_id
,title
order by
rate desc
limit 5;
2025-01-15 计算完播率(按人数) 
select
a.video_id
,title
,round(count(distinct case whentimestampdiff(second,start_time,end_time)>=duration then uid else null end)/count(distinct uid)*100,2) rate
from 
ks_video_wat_log a 
left join
ks_video_inf b
on 
a.video_id=b.video_id
where 
 date(start_time)>=date_sub(current_date,interval 1 month)
 group by
a.video_id
,title
order by
rate desc
limit 5;
2025-01-15 计算完播率(按次数) 
select
a.video_id
,title
,round(sum(case whentimestampdiff(second,start_time,end_time)>=duration then 1 else 0 end)/count(1)*100,2) rate
from ks_video_wat_log a 
left joinks_video_inf b
on a.video_id=b.video_id
where 
 date(start_time)>=date_sub(current_date,interval 1 month)
 group by
 	a.video_id
,title
order by
	rate desc
limit 5;
2025-01-15 总播放时长最长的视频 
select
 a.video_id
,title
,round(sum(timestampdiff(second,start_time,end_time))/3600,2)	total_play_duration_hours
from 
ks_video_wat_log a
 left joinks_video_inf b
 on a.video_id=b.video_id
 where 
 date(start_time)>=date_sub(current_date,interval 1 month)
 group by
 a.video_id
,title
 order by
 		total_play_duration_hours desc
 limit 5;
2025-01-15 总播放时长最长的视频 
select
	a.video_id
,title
,round(sum(timestampdiff(minute,start_time,end_time))/60,2)
from 
 	ks_video_wat_log a
 left joinks_video_inf b
 on a.video_id=b.video_id
 where 
 	date(start_time)>=date_sub(current_date,interval 1 month)
 group by
 	a.video_id
,title
order by
	round(sum(timestampdiff(minute,start_time,end_time))/60,2) desc
 limit 5
2025-01-15 总播放时长最长的视频 
select
	a.video_id
,title
,round(sum(timestampdiff(minute,start_time,end_time))/60,2)
from 
 	ks_video_wat_log a
 left joinks_video_inf b
 on a.video_id=b.video_id
 where 
 	date(start_time) >=date_sub(start_time,interval 1 month)
 group by
 	a.video_id
,title
order by
	round(sum(timestampdiff(minute,start_time,end_time))/60,2) desc
 limit 5
2025-01-15 总播放时长最长的视频 
select
	a.video_id
,title
,round(sum(timestampdiff(minute,start_time,end_time))/60,2)
from 
 	ks_video_wat_log a
 left joinks_video_inf b
 on a.video_id=b.video_id
 where 
 	date(start_time) >=date_sub(start_time,interval 1 day)
 group by
 	a.video_id
,title
order by
	round(sum(timestampdiff(minute,start_time,end_time))/60,2) desc
 limit 5