题解 | 未完成率top50%用户近三个月答卷情况

with UncompletedUsersRank as 
(
    select
        er.uid,
        sum(case when er.submit_time is null then 1 else 0 end) as incomplete_cnt,
        count(er.start_time) as total_cnt,
        sum(case when er.submit_time is null then 1 else 0 end) / count(er.start_time) as uncomplete_rate,
        percent_rank() over(order by sum(case when er.submit_time is null then 1 else 0 end) / count(er.start_time) desc) as uncomplete_rank
    from
        exam_record er 
    left join
        examination_info ei on er.exam_id = ei.exam_id
    where 
        ei.tag = 'SQL'
    group by 
        er.uid
),

RecentThreeMonthsCnt as 
(
    select
        er.uid,
        date_format(er.start_time,'%Y%m') as exam_month,
        start_time,
        submit_time,
        exam_id,
        dense_rank() over(partition by er.uid order by date_format(er.start_time,'%Y%m') desc) as ranking
    from
        exam_record er
    left join
        user_info ui on er.uid = ui.uid
    where 
        ui.level in (6,7)
)

select
    rt.uid,
    rt.exam_month as start_month,
    count(rt.start_time) as total_cnt,
    sum(case when rt.submit_time is not null then 1 else 0 end) as complete_cnt
from
    RecentThreeMonthsCnt rt
left join
    UncompletedUsersRank uu on rt.uid = uu.uid
where 
    rt.ranking <= 3 and uu.uncomplete_rank <= 0.5
group by 
    rt.uid,
    rt.exam_month
order by 
    1,2