题解 | #未完成率较高的50%用户近三个月答卷情况#
未完成率较高的50%用户近三个月答卷情况
http://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c
with tt as (select uid,(count(start_time)-count(submit_time))/count(start_time) as rate,
row_number()over(order by (count(start_time)-count(submit_time))/count(start_time))as ranking
from exam_record er inner join examination_info ei on er.exam_id=ei.exam_id
where tag='SQL'
GROUP BY uid)
select uid,start_month,count(start_time),count(submit_time)
from(
select *,date_format(start_time,'%Y%m') as start_month,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc) ranking
FROM exam_record
where uid in
(select nt.uid
from
(select uid,rate,ranking
from tt
where ranking>
(select max(ranking)
from tt)/2) nt inner join user_info ui on nt.uid=ui.uid and ui.level in(6,7)))nnt
where ranking<=3
group by uid,start_month
row_number()over(order by (count(start_time)-count(submit_time))/count(start_time))as ranking
from exam_record er inner join examination_info ei on er.exam_id=ei.exam_id
where tag='SQL'
GROUP BY uid)
select uid,start_month,count(start_time),count(submit_time)
from(
select *,date_format(start_time,'%Y%m') as start_month,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc) ranking
FROM exam_record
where uid in
(select nt.uid
from
(select uid,rate,ranking
from tt
where ranking>
(select max(ranking)
from tt)/2) nt inner join user_info ui on nt.uid=ui.uid and ui.level in(6,7)))nnt
where ranking<=3
group by uid,start_month
order by uid,start_month
需要注意的是，这个题目的要求是，如果只有5个人，那么前3都算前50%。如果只有4个人，那么前2算50%。在奇数人数的处理上，题目的50%，其实有问题。
