冷凡社长

分类

题解(2)

/ 注册

TA的专栏 0篇文章 0人订阅

0篇文章 0人学习

全部文章（共133篇）

题解 | #每个创作者每月的涨粉率及截止当前的总粉丝量#

select author,date_format(start_time,'%Y-%m') as month, round((sum(case when if_follow =1 then 1 else 0 end) - sum(case when if_follow =2 th...

Mysql

2022-08-24

1 239

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

select tag,sum(if_retweet) as retweet_cut, round(sum(if_retweet)/count(*),3) as retweet_rate from tb_user_video_log join tb_video_info using(video_id...

Mysql

2022-08-24

1 284

题解 | #平均播放进度大于60%的视频类别#

with t1 as ( select video_id,tag, timestampdiff(second,start_time,end_time)/duration as play_rate from tb_user_video_log join tb_video_info using(vid...

Mysql

2022-08-24

1 210

题解 | #各个视频的平均完播率#

select video_id, round(sum(case when timestampdiff(second,start_time,end_time)>= duration then 1 else 0 end )/count(*),3) as avg_comp_play_rate...

Mysql

2022-08-24

1 226

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

select t1.uid,level,register_time ,max(score) as max_score from exam_record t join examination_info using(exam_id) join user_info t1 on t.uid=t1....

Mysql

2022-08-24

4 219

题解 | #注册时间最早的三个人#

select uid,nick_name,register_time from user_info order by register_time limit 3

Mysql

2022-08-24

1 223

题解 | #根据指定记录是否存在输出不同情况#

with t1 as ( -- 形成一张大表，包含所有uid的计算要素 select uid,count(t.start_time) as num, case when count(t.start_time)=0 then 0 else count(submit_time is null or nu...

Mysql

2022-08-24

1 288

题解 | #筛选限定昵称成就值活跃日期的用户#

select uid,nick_name,achievement from user_info where nick_name like "牛客%号" and achievement between 1200 and 2500 and uid in ( selec...

Mysql

2022-08-24

1 257

题解 | #0级用户高难度试卷的平均用时和平均得分#

select uid, round(avg(case when score is null then 0 else score end)) as avg_score, round(avg(case when score is null then duration else timestampdif...

Mysql

2022-08-24

3 268

题解 | #统计有未完成状态的试卷的未完成数和未完成率#

select exam_id,count(submit_time is null or null) as incomplete_cnt, round(count(submit_time is null or null)/count(*),3) as complete_rate from exam_r...

Mysql

2022-08-24

1 265