题目复盘——窗口函数2
复盘两个难题
SQL31 未完成率较高的50%用户近三个月答卷情况
问题分析
(1)SQL试卷上未完成率较高的50%用户——
未完成率:1-count(submit_time)/count(start_time) incomplete_rate from exam_record e_r left join examination_info e_i on e_r.exam_id=e_i.exam_id where tag=‘SQL’;
窗口函数排序:percent_rank()over(order by 未完成率 desc) ranking1;
较高的50%用户:ranking1<=0.5;
(2)6级和7级用户——在(1)筛选后的基础上(或者同时在where里用and并列筛选)left join user_info u_i on …where level in (6,7)或者子查询where uid in(select uid from user_info where level in (6,7));
(3)有试卷作答记录的近三个月——
窗口函数排序:dense_rank()over(partition by uid order by date_format(start_time,’%Y%m’) desc) ranking2
筛选近三个月:ranking2<=3;
(4)每个月答卷数目和完成数目——count(start_time) total_cnt;count(submit_time) complete_cnt group by uid,start_month;
(5)用户ID、月份升序排列——order by uid asc,start_month asc;
答案重写
SELECT uid,start_month,total_cnt,complete_cnt
FROM
(SELECT e_r.uid uid,date_format(start_time,'%Y%m') start_month,count(start_time) total_cnt,count(submit_time) complete_cnt,
dense_rank()over(partition by uid order