题解 | #连续两次作答试卷的最大时间窗#

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 (partition by uid order by date_format(start_time,'%Y%m') desc) t_rank
from exam_record
where uid in 
(select a.uid from 
(select uid,
avg(case when submit_time is null then 1 else 0 end) incomplete_rate,
percent_rank() over (order by avg(case when submit_time is null then 1 else 0 end) desc) pr
from exam_record r inner join examination_info i
on r.exam_id = i.exam_id
where i.tag = 'SQL'
group by uid) as a 
inner join user_info i on i.uid = a.uid
where (i.level>5)
and pr <= 0.5)
group by uid,start_month
order by uid,start_month) as b