题解 | #近三个月未完成试卷数为0的用户完成情况#

with table1 as (
    select uid, 
    date_format(start_time,'%Y%m') as time,
    dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) as start_rank,
    submit_time, 
    dense_rank() over(partition by uid order by date_format(submit_time,'%Y%m') desc) as submit_rank
    from exam_record
),
# 统计 近 三个有答题记录的月份
table2 as (
    select uid, time, 
    sum(case when time is not null then 1 else 0 end) as start_number ,# 当月作答次数
    sum(if(submit_time is not null , 1 ,0)) as submit_number           # 当月完成次数
    # 月份排序  以便筛选出  近三个月
    from table1
    group by uid,time

 ),
# # 筛选出近三个月 有作答记录  且   作答都完成的
table3 as (
    select uid,time,start_number,submit_number,
    dense_rank()over(partition by uid order by time desc) as rank_time
    from table2
),
table4 as(
select uid,time,start_number,submit_number
from table3
where rank_time <= 3
)
select uid,  sum(submit_number) as exam_complete_cnt
from table4
group by uid
having sum(start_number) = sum(submit_number) 
order by exam_complete_cnt desc ,uid desc