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

首先找出未完成率前50%的人。

在子查询中算出未完成率：1-count(submit_time)/count(start_time)，此处要将exam_record和examination_info两表连接来将tag限制为"SQL"。然后用percent_rank得到每个用户未完成率的百分位，将这一层查询也作为子查询。最后选出uid并限制百分位大于等于0.5. 剩下的和上一题差不多。

with incom_user as 
(select uid from
(select uid, percent_rank() over (order by incomplete_rate) as per_ranking
from
(select uid,
1- count(submit_time) / count(start_time) as incomplete_rate
from exam_record inner join examination_info using(exam_id)
where tag = "SQL"
group by uid) as temp) as temp2
where per_ranking >= 0.5)

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 *,
dense_rank() over (partition by uid order by date_format(start_time, "%Y%m") desc) as ranking
from exam_record) as temp3
right join user_info using(uid) 
inner join incom_user using(uid)
where ranking <= 3 and (level = 6 or level = 7)
group by uid, date_format(start_time, "%Y%m")
order by uid, start_month