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

虽然搞不懂，但还是自己敲了一遍，搬砖的一天

select er.uid,
date_format(er.start_time,'%Y%m') as start_month,
count(er.id) as total_cnt,
sum(case when er.submit_time is not null then 1 else 0 end) as complete_cnt
from 
(select *,
dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) as month_rank from exam_record)er
left join user_info on er.uid=user_info.uid
where user_info.level in (6,7) and er.month_rank<=3 and er.uid in 
(select b.uid
from 
(select a.*,
row_number() over(order by a.incomplete_rate desc,a.uid asc) as rank_num
from
(select uid,
sum(case when submit_time is null then 0 else 1 end)as incomplete_cnt,
count(id) as total_cnt,
sum(case when submit_time is null then 0 else 1 end)/count(id) as incomplete_rate
from exam_record
group by uid)a)b left join user_info u on b.uid=u.uid
where b.rank_num>(select count(distinct uid)/2 from exam_record) and  level in (6,7))
group by 1,2
order by 1,2