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

with t as(
    select uid,
           level,
          (1 - count(submit_time) / count(start_time)) as incomplete_rate,
          percent_rank() over(order by (1 - count(submit_time) / count(start_time)) desc) ranking
    from exam_record
    join examination_info ei
    using(exam_id)
    join user_info
    using(uid)
    where tag = 'SQL'
    group by uid
)



select uid,
		date_format(start_time,'%Y%m') as start_month,
		count(start_time) as total_cnt,
		count(submit_time) as complete_cnt
from (
		select uid,
        start_time,
        submit_time,
		dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) as recent
		from exam_record er
		join examination_info ei
		using(exam_id)
		where uid in(
				select uid     
				from t
				where ranking <= 0.5
				and (level = 6 or level = 7)
		)
)t2
where recent <=3
group by uid,start_month
order by uid,start_month