题解 | #未完成率较高的50%用户近三个月答卷情况#
未完成率较高的50%用户近三个月答卷情况
http://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c
select t4.uid,
extract(year_month from t4.start_time) as start_month,
count(t4.start_time) as total_cnt,
count(t4.submit_time) as complete_cnt
from
(select er.uid,
er.start_time,
er.submit_time,
dense_rank() over(order by extract(year_month from er.start_time) desc) as dr
from
(select t3.uid
from
(select t2.uid
from
(select uid,
cume_dist() over(order by incomplete_rate) as rn
from
(select uid,
(count(start_time) - count(submit_time)) as incomplete_cnt,
count(start_time) as total_cnt,
((count(start_time) - count(submit_time)) / count(start_time)) as incomplete_rate
from examination_info ei inner join exam_record er on ei.exam_id=er.exam_id
where tag='SQL' and start_time is not null
group by uid) t1) t2
where t2.rn > 0.5) t3 inner join user_info ui on t3.uid=ui.uid
where ui.level in (7,6)) t3 inner join exam_record er on t3.uid=er.uid) t4
where t4.dr in (1,2,3)
group by t4.uid,extract(year_month from t4.start_time)
order by uid,start_month;