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

select uid, start_month, total_cnt, complete_cnt
from
(select t.uid uid, date_format(start_time, '%Y%m') start_month, count(t.uid) total_cnt, count(score) complete_cnt, rank() over (partition by t.uid order by date_format(start_time, '%Y%m') desc) r
from
(select uid, count(uid) - count(score) incomplete_cnt, count(uid) total_cnt,
percent_rank() over (order by ((count(uid) - count(score)) / count(uid))) incomplete_rate
from exam_record r
left join examination_info i
on r.exam_id = i.exam_id
where tag = 'SQL'
group by uid) t
left join user_info u
on t.uid = u.uid
left join exam_record e
on t.uid = e.uid
where (level = 6 or level = 7) and incomplete_rate >= 0.5
group by t.uid, start_month) a
where r <= 3
order by uid, start_month;

使用percent_rank()排名窗口函数筛选未完成率较高的前50%用户。