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

with t as(
select uid,sum(if(submit_time is null,1,0)) as incomplete_cnt,count(start_time) as total_cnt,sum(if(submit_time is null,1,0))/count(start_time) as incomplete_rate,row_number() over(order by sum(if(submit_time is null,1,0))/count(start_time) desc) as rk from exam_record left join examination_info using(exam_id) where tag='SQL' group by 1),
t1 as (
select uid from t left join user_info using(uid) where rk<= round((select max(rk) from t)/2) and level in (6,7))

select uid,start_month,total_cnt,complete_cnt from(
select uid,DATE_FORMAT(start_time,'%Y%m') as start_month,count(start_time) as total_cnt,count(score) as complete_cnt,ROW_NUMBER() over(partition by uid order by DATE_FORMAT(start_time,'%Y%m') desc) as rk from t1 left join exam_record using(uid) group by 1,2) tmp where rk<=3 order by 1,2