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

#需求：统计SQL试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目
#输出：uid、start_month(月份)、total_cnt(作答数)、complete_cnt(完成数)
#要求：1.对用户的筛选：按未完成率高到低排序的前50%中，level in (6,7) 的用户
#    2.时间范围：有作答记录的近三个月
#    3.指标输出：按月划分，按uid，month升序
with w1 as(
  select uid
  from(
    select uid,percent_rank()over(order by count(submit_time)/count(start_time)) ck1
    from exam_record
    where exam_id in(select exam_id from examination_info where tag='SQL')
    group by 1
  ) t1
  where ck1<=0.5
  and uid in(select uid from user_info where level in(6,7))
)
#取得符合筛选的用户
#level和tag的筛选写在同一层，提示数组越界，分开写则没有
select uid,date_format(start_time,'%Y%m') start_month,
count(start_time) etotal_cnt,count(submit_time) total_cnt
from w1 left join(
    select*,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc) ck2
    from exam_record
) w3
#开窗完成对月份的排序，后续筛选近三月
#这里初次写的时候直接写不套子查询，返回提示数组越界，嵌套子查询在外层完成对ck1的筛选则没有这个问题
using(uid)
where ck2<=3
group by 1,2
order by 1,2
复盘：原本对于用户的筛选代码如下
with w1 as(
select uid
from(
    select uid,start_time,submit_time
    from exam_record join user_info using(uid)
    join examination_info using(exam_id)
    where tag='SQL' and level in(6,7)
) w2
group by 1
having 1-(count(submit_time)/count(start_time))>=0.5
)
自测是通过的，但提交错误，核查提交处的参考答案为1003用户的数据，而看源数据表，1003是5level用户，不符合要求条件，暂且归咎出题有问题
percent_rank是按百分比将完成率排序，where筛选<=50%的用户，即符合题意要求的未完成率前50%高的用户
而第41行的having，则是把要求中的逻辑更完整的展现出来，1-完成率=未完成率，选其中>=50%的