select date_format(in_time,'%Y-%m-%d') dt ,round(sum(timestampdiff(second,in_time,out_time))/count(distinct uid),1) avg_viiew_len_sec from tb_user_log where artical_id != 0 and date_format(in_time,'%Y-%m') = '2021-11' group by 1 order by 2;

#本题有很多细节要求，如top3视频，近一个月发布视频，按照结束时间计算最近时间，已经最近播放时间的判断。 select video_id ,round((100*r1+5*r2+3*r3+2*r4)/(r5+1),0) hor_index from ( select tu.video_id video_id ,round(sum(if(timestampdiff(second,start_time,end_time) >= duration,1,0))/count(start_time),1) r1#视频完播率 ,sum(if_like) r2#点赞数 ,sum(if(comment_...

select author ,month ,round((ifo - info)/cnt,3) fans_growth_rate ,sum(ifo)over(partition by author order by month) - sum(info)over(partition by author order by month) total_fans from ( select author ,date_format(start_time,'%Y-%m') month ,sum(if(if_follow = 1,1,0)) ifo#当月涨粉数 ,sum(if(if_follow = 2,1,...

select tag ,concat(round((td/st)*100,2),'%') avg_play_progress from ( select tag ,sum(case when timestampdiff(second,start_time,end_time) >= duration then 1 else timestampdiff(second,start_time,end_time)/duration end) td ,count(start_time) st from tb_user_video_log tu left join tb_video_info tv o...

#先计算出所用用户的试卷未完成数和未完成率。因为是互斥事件，所以分开筛选再用union all合并 with table1 as ( select ui.uid uid ,level ,if(count(start_time)-count(submit_time)>0,count(start_time)-count(submit_time),0) incomplete_cnt ,if(round((count(start_time)-count(submit_time))/count(start_time),3) is null ,0.000,round((count(start_tim...

select * from ( select uid ,case when substring(start_time,1,7) = '2021-09' or substring(submit_time,1,7) = '2021-09' then nick_name else null end nick_name ,achievement from ( #以用户信息表作为主表，试卷作答记录表和练习作答记录表连接，并且筛选出试卷作答和练习作答的最近一次时间 select ui.uid uid ,nick_name ,start_time ,pr.submit_time submit_time ,a...

select er.uid ,round ( avg( case when submit_time is null then 0 else score end ),0 ) avg_score ,round ( sum( #未完成直接取试卷时间duration case when submit_time is null then duration else timestampdiff(minute,start_time,submit_time) end )/count(start_time),1 ) abg_time_took from exam_record er left join exam...

select exam_id ,incomplete_cnt ,round(incomplete_cnt/cnt,3) complete_rate from ( select exam_id ,count(start_time) cnt ,sum(if(submit_time is null,1,0)) incomplete_cnt from exam_record group by 1 )table1 where incomplete_cnt != 0

#分两步，先查出每个月的提交数，再利用子查询查找截止到当天的提交数 select * ,sum(month_cnt)over(partition by exam_id order by start_month ) cum_exam_cnt from ( select distinct exam_id ,substring(replace(start_time,'-',''),1,6) start_month ,count(start_time)over(partition by exam_id,substring(replace(start_time,'-',''),1,6)) month_c...

#先求出最大值和最小值创建新表，再对新表中的数据进行计算和筛选。 select uid ,exam_id ,round(avg(avg_new_score),0) from ( select uid ,exam_id ,case when maxx = minx then score else (score-minx)/(maxx-minx)*100 end avg_new_score from ( select uid ,er.exam_id ,score ,min(score)over(partition by er.exam_id) minx ,max(score)over(partit...

#需要按tag进行分类，筛选出2020和2021各上半年的数据，表格整合，最后进行筛选 #这里要注意出现计算为负数的情况会报错 select tag ,exam_cnt_20 ,exam_cnt_21 ,concat(round(((exam_cnt_21-exam_cnt_20)/exam_cnt_20)*100,1),'%') growth_rate ,exam_cnt_rank_20 ,exam_cnt_rank_21 ,case when exam_cnt_rank_21 >= exam_cnt_rank_20 then round((exam_cnt_rank_21-exam_...

#先统计每个人的SQL试卷上未完成率前50%的用户，再进行用户等级检索，再根据要求筛选数据 select uid ,substring(replace(start_time,'-',''),1,6) start_month ,count(*) total_cnt ,count(submit_time) complete_cnt from ( select uid ,start_time ,submit_time ,dense_rank()over(partition by uid order by substring(start_time,1,7) desc) rk#不跳过排名存在并列情况 f...

#2021年试卷活跃天数selectui.uid uid,count(distinct ui.uid,substring(er.start_time,1,10)) act_days_2021_examfrom user_info ui left join exam_record eron ui.uid = er.uid and  year(start_time) = 2021where level >= 6group by ui.uid#2021年答题活跃天数selectui.uid uid,count(distinct ui.uid,substring(pr.submit_time,1...

select table2.uid ,act_month_total ,act_days_2021 ,act_days_2021_exam ,act_days_2021_question from ( select uid ,count(distinct t1) act_month_total ,count(distinct if(year(t2) = 2021,t2,null)) act_days_2021 from ( select ui.uid uid ,substring(start_time,1,7) t1 ,substring(start_time,1,10) t2 from us...