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

with table1 as (
    select uid , 
    date_format(start_time,'%Y%m') as time,
    submit_time,
    dense_rank() over(partition by uid order by date_format(start_time,'%Y%m') desc) as time_rank
    from exam_record
),
table2 as (
    select uid ,
    sum(if(time is not null , 1 , 0)) as exam_all_cnt,
    sum(if(submit_time is not null , 1 , 0)) as exam_complete_cnt
    from table1 
    where time_rank <= 3
    group by uid
    having exam_complete_cnt = exam_all_cnt
)

select uid , exam_complete_cnt
from table2
order by exam_complete_cnt desc ,uid desc