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

with t as(
    select n.uid,incomplete_cnt,total_cnts,incomplete_rate
    from(
        select exam_record.uid,sum(if(submit_time is null,1,0)) as incomplete_cnt,count(start_time) as total_cnts,(sum(if(submit_time is null,1,0))/count(start_time)) as incomplete_rate,percent_rank()over(order by (sum(if(submit_time is null,1,0))/count(start_time))) as p
        from exam_record
        inner join examination_info
        on examination_info.exam_id=exam_record.exam_id
        where tag="SQL"
        group by exam_record.uid
    ) as n
    where p>=0.5
)
select n2.uid,start_month,total_cnt,complete_cnt
from(
    select exam_record.uid,date_format(start_time,'%Y%m') as start_month,count(start_time) as total_cnt,count(submit_time) as complete_cnt,dense_rank()over(partition by exam_record.uid order by date_format(start_time,'%Y%m') desc) as r 
    from exam_record
    inner join user_info
    on user_info.uid=exam_record.uid
    where level in ('6','7') and exam_record.uid in (
        select uid from t
    )
    group by date_format(start_time,'%Y%m'),exam_record.uid
) as n2
where r<=3
order by n2.uid,start_month