题解 | 未完成率top50%用户近三个月答卷情况

#SQL试卷上未完成率较高的50%用户
with goal as(
select uid from
(select uid,
row_number() over(order by complete_rate) as rk,
count(*) over () as num
from(
select uid,
round(count(submit_time)/count(start_time),2) as complete_rate

from exam_record er
join examination_info ei on er.exam_id = ei.exam_id
where tag='SQL'

group by uid
order by complete_rate desc
)a
)b
where rk<=0.5*(num+1))

select uid,start_month,total_cnt,complete_cnt
from(
select er.uid,
date_format(start_time,'%Y%m') as start_month,
dense_rank() over(order by date_format(start_time,'%Y%m')  desc) as ranking,
count(*) as total_cnt,
count(submit_time) as complete_cnt
from goal
join user_info ui on goal.uid = ui.uid
join exam_record er on er.uid = ui.uid

where level =6 or level =7

group by er.uid,date_format(start_time,'%Y%m'))c
where ranking<4
order by uid,start_month

思路顺序：

1. 求SQL试卷上未完成率较高的50%用户：这里跟中位数的做法比较像，通过where rk<=0.5*(num+1)实现
2. 在1基础上求6级和7级用户，通过两表连接实现
3. 求每用户每个月的答卷数目和完成数目，简单聚合实现
4. 求近三月，在3基础上用一个row_number给出每月的ranking，在外层写一个限制条件只取近三月