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

/*
试卷上未完成率较高的50%用户中，6级和7级用户在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目。按用户ID、月份升序排序。
--1.sql试卷未完成率较高的50%用户
--未完成率= 未完成试卷数/试卷总数;
--计算出未完成试卷数，试卷总数，运用窗口函数，以未完成率排序降序，得到名次
--满足未完成率较高的50%的用户，所以，满足排名<= 0.5*总用户数
--2.6.7级用户的近三个月作答记录，每个月的答题数目与完成数目
--先找出三个月的做答记录,分别计算出答题数目和完成数目
--运用表连接，找出level>=6的用户数*/

select 
b.uid,d.mon,d.total_cnt,d.com_cnt
from(
        select a.uid
        from ( 
            select uid,count(*) as total_cnt,sum(if(score is null,1,0)) as incom_cnt
            ,sum(if(score is null,1,0))/count(*) as in_rate
            ,count(*)over() as num
            ,row_number() over(order by (sum(if(score is null,1,0))/count(*)) desc) as in_rate_num
            from exam_record 
            where exam_id in ('9001','9002')
            group by uid
         )a 
             join user_info u on a.uid = u.uid
             where u.level>= 6 and ceiling(a.num*0.5)>= a.in_rate_num
     )b
 left join 
 (select c.uid,c.mon,c.total_cnt,c.com_cnt
    from 
        (
         select uid
         ,date_format(start_time,'%Y%m') as mon
         ,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc)as num
         ,count(*) as total_cnt
         ,sum(if(score is not null,1,0)) as com_cnt
         from exam_record e
         group by 1,2
        )c
     where c.num<=3
     )d 
     on b.uid= d.uid
order by 1,2
#
# select c.uid,c.mon,c.total_cnt,c.com_cnt
#     from 
#         (
#          select uid
#          ,date_format(start_time,'%Y%m') as mon
#          ,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc)as num
#          ,count(*) as total_cnt
#          ,sum(if(score is not null,1,0)) as com_cnt
#          from exam_record e
# #          where e.exam_id in('9001','9002')
#          group by 1,2
#         )c
#      where c.num<=3
# #      )d
# #      on b.uid= d.uid
#    and c.uid in 
#    (
#        select f.uid
#     from(
#         select a.uid
#         from ( 
#             select uid
#             ,count(*) as total_cnt
#             ,sum(if(score is null,1,0)) as incom_cnt
#             ,count(uid)over() as num 
#             ,row_number() over(order by (sum(if(score is null,1,0))/count(*)) desc) as in_rate_num
#             from exam_record 
#             group by uid
#          )a 
#        where ceiling(a.num*0.5) >= a.in_rate_num
#       )f 
#     join user_info u 
#        on f.uid = u.uid
#        where u.level>= 6 
#      )
# order by 1,2