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

Select uid, start_month, count(start_month) total_cnt, count(submit_month) complete_cnt
from 

(Select er.uid, tag, date_format(start_time, '%Y%m') start_month, date_format(submit_time, '%Y%m') submit_month 
from 
exam_record er 
left join examination_info ei on er.exam_id = ei.exam_id 
left join user_info ui on er.uid = ui.uid 
where level in ('6','7') ) t1

where 
t1.uid in (
    Select uid
    from 
        (Select uid, total_cnt , incomplete_cnt, ROW_NUMBER() over(order by incomplete_cnt/total_cnt ) as incomplete_rank, count(1)over() as num
        from 
            (Select er.uid as uid, count(start_time) as total_cnt, count(submit_time) as incomplete_cnt
            from (exam_record er 
            left join examination_info ei on er.exam_id = er.exam_id 
            left join user_info ui on er.uid = ui.uid)
            where tag = "SQL"
            group by er.uid) t2 ) t3 
    where incomplete_rank <= (num + 1) / 2
) and 
t1.start_month in (Select start_month from (Select date_format(start_time, '%Y%m') as start_month, DENSE_RANK() over(partition by uid order by date_format(start_time, '%Y%m') desc) as start_month_rank from exam_record) t4 where start_month_rank <= 3)


group by t1.uid, t1.start_month
order by uid,start_month