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

with temp as (select er.*
                   , dense_rank() over (partition by ui.uid order by month(start_time) desc ) r
              from exam_record er
                       inner join user_info ui on er.uid = ui.uid
              where ui.uid in (SELECT A.uid
                               FROM (SELECT uid,
                                            PERCENT_RANK() over ( ORDER BY count(submit_time) / count(start_time) ) rate_rk -- 对完成率进行分数排序
                                     FROM exam_record
                                     WHERE exam_id IN
                                           (SELECT exam_id FROM examination_info WHERE tag = 'SQL') -- SQL试卷
                                     GROUP BY uid) A
                               WHERE rate_rk <= 0.5 -- 查找排名低于50%的用户
                                 AND uid IN (SELECT uid FROM user_info WHERE level IN (6, 7))))
select uid
     , date_format(start_time, '%Y%m') start_month
     , count(start_time)               total_cnt
     , count(score)                    complete_cnt
from temp
where r <= 3
group by uid, start_month
order by uid, start_month;