无上清颜

题解

/ 注册

全部文章 / 题解（共49篇）

题解 | #近一个月发布的视频中热度最高的top3视频#

**最大值max窗口函数分别以start_time和end_time为标准竟然结果不同... 这个题告诫我要早睡😅 ** select video_id,round((sum(TIMESTAMPDIFF(second,start_time,end_time)>=duration)/co...

Mysql

2021-12-12

1 640

题解 | #每类视频近一个月的转发量/率#

select author,month,fans_growth_rate,sum(fans)over(partition by author order by month) as total_fans from (select author,substr(start_time,1,7) as mo...

Mysql

2021-12-10

1 378

这波用一下date_add

难点：筛选最近30天的数据 select tag,sum(if_retweet) as retweet_cnt,round(sum(if_retweet)/count(1),3) as retweet_rate from tb_user_video_log t join tb_video_info ...

Mysql

2021-12-10

3 518

继续TIMESTAMPDIFF

select tag, concat(round(avg(if(TIMESTAMPDIFF(second,start_time,end_time)>=duration,1,TIMESTAMPDIFF(second,start_time,end_time)/duration))*100,2) ,...

Mysql

2021-12-10

1 373

就是说 TIMESTAMPDIFF YYDS

select t.video_id,round(sum(TIMESTAMPDIFF(second,start_time,end_time)>=duration)/count(1),3) as avg_comp_play_rate from tb_user_video_log t join tb...

Mysql

2021-12-10

1 406

题解 | #对过长的昵称截取处理#

select uid,if(CHAR_LENGTH(nick_name)<=13,nick_name,CONCAT(substring(nick_name,1,10),"...")) as nick_name from user_info where CHAR_LENGTH(nick_name...

Mysql

2021-12-09

1 440

题解 | #注册当天就完成了试卷的名单第三页#

with t as ( select t.uid,level,register_time,start_time,score,tag from user_info t join examination_info t1 join exam_record t2 on t.uid = t2....

Mysql

2021-12-08

1 519

题解 | #21年8月份练题总数#

select count(distinct device_id) as did_cnt,count(1) as question_cnt from question_practice_detail where DATE_FORMAT(date,"%Y%m") = '202108'

Mysql

2021-12-07

1 567

都稍一稍还有比这个更香的解法？

select device_id,university,gpa from user_profile where (university,gpa) in (select university,min(gpa) from user_profile group by university) order b...

Mysql

2021-12-07

1112 29467

题解 | #各用户等级的不同得分表现占比#

with t as (select t.uid,exam_id,score,level, case when score>=90 then '优' when score>=75 and score<90 then '良' ...

Mysql

2021-12-03

1 476