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

select uid,start_month,total_cnt,complete_cnt
from(
select uid,start_month,total_cnt,complete_cnt,dense_rank() over(partition by uid order by start_month desc) rankk
from(
select uid,date_format(start_time,"%Y%m") start_month, count(*) total_cnt,count(submit_time) complete_cnt
from exam_record
where uid in(
select uid
from(
select uid,PERCENT_RANK()over( ORDER BY incomplete_rate) rate,level
from(
select t1.uid,(count(t1.start_time)-count(t1.submit_time))/count(t1.start_time) incomplete_rate,t2.level
from exam_record t1 
left join user_info t2
on t1.uid= t2.uid
where t1.exam_id in (
    select exam_id 
    from examination_info
    where tag = "SQL"
)
group by t1.uid) t3) t4
where level > 5
and rate >= 0.5)

group by uid,start_month) t5) t6
where rankk <= 3
order by uid asc,start_month asc
注意筛选用户时用的是SQL类型的试卷，最后统计时用的是所有类型的试卷。