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

# 思路：
# 1、按照sql试卷未完成率高的前50%的用户中，6、7级用户筛选出来
# 做法：（1）未完成率前50%，row_number() count()
# （2）筛选6 7级用户
# 2、将满足条件用户的作答记录从exam_record中挑选出来，计算答卷数目、完成数目
# 做法：（1）将日期用date_format转换成年月形式，通过dense_rank排序，按照排名挑选最近三个月记录
# （2）计算答卷数目和完成数目

select distinct t3.uid,t3.start_time,
count(t3.start_time) over(partition by t3.uid,t3.start_time) as total_cnt
,count(t3.submit_time) over(partition by t3.uid,t3.start_time) as complete_cnt 
from (
select e2.uid,date_format(e2.start_time,'%Y%m') as start_time
,e2.submit_time
,DENSE_RANK() over(partition by e2.uid order by date_format(e2.start_time,'%Y%m') desc ) as rank_start_time 
from exam_record as e2 where 
e2.uid in (
select t3.uid from (
select t1.uid,t1.imcom_rate,t1.level 
,percent_rank() over(order by imcom_rate desc) as rank_imcom 
from (
select e1.uid,u1.level 
    ,(count(*)-count(e1.submit_time))/count(*) as imcom_rate
from exam_record as e1 
    left join user_info as u1 on e1.uid=u1.uid 
    group by e1.uid) t1) t3 
where rank_imcom<=0.5  and t3.level in (6,7))) t3 
where t3.rank_start_time <=3 order by t3.uid,t3.start_time