题解 | #未完成率较高的50%用户近三个月答卷情况#

复杂点在于找到符合题意的未完成率较高的前50%人。

#2.按第一步求出来之后按条件罗列即可。
select uid,start_month,total_cnt,complete_cnt from (
(select uid,date_format(start_time,'%Y%m') start_month,count(start_time) total_cnt,count(submit_time) complete_cnt ,
dense_rank()over(order by date_format(start_time,'%Y%m')  desc) rk
from exam_record 
group by uid,start_month ))a
where uid in
#1.按未完成率倒序，同时统计列表人数。未完成率前50%，即排名>=总人数/2
#这里需要注意不要用dense_rank,如果使用dense，两人未完成率相同时会未完成人数比总人数少，
#导致有符合条件的人员未被包含。
(select uid from (
select e1.uid,rank()over(order by count(submit_time)/count(start_time) desc) rk,count(e1.uid)over() cnt 
    from exam_record e1 left join examination_info e2
    on e1.exam_id=e2.exam_id
    where tag='sql'
    group by e1.uid
) a 
where rk>cnt/2 #求未完成率前50%的人
and uid in (select uid from user_info where level in (6,7)))
and rk <=3 #题目要求的近三个月
order by uid,start_month