题解 | #连续两次作答试卷的最大时间窗#
未完成率较高的50%用户近三个月答卷情况
http://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c
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
(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
where t_rank <= 3
这题难在未完成率较高的50%用户中的理解上，理清楚就好了。
