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

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