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

with t as (
select uid,dt from(
select uid,dt,row_number() over(partition by uid order by dt desc) as rk from(
select distinct DATE_FORMAT(start_time,'%Y-%m') as dt,uid from exam_record) tmp ) a where rk<=3)

select uid,a1 as exam_complete_cnt from(
select t.uid,count(start_time) as a1,count(submit_time) as a2 from exam_record e right join t on t.uid=e.uid and dt=DATE_FORMAT(start_time,'%Y-%m') group by 1 having a1=a2) tmp order by 2 desc,1 desc