题解 | #近三个月未完成试卷数为0的用户完成情况#
近三个月未完成试卷数为0的用户完成情况
https://www.nowcoder.com/practice/4a3acb02b34a4ecf9045cefbc05453fa
select
t0.uid
,sum(t0.al) exam_complete_cnt
# ,sum(t0.alsub)
from
(
select uid
,date_format(start_time,'%Y%m') mon
,count(exam_id) al
,count(submit_time) alsub
from exam_record
group by uid, date_format(start_time,'%Y%m')
) t0
join
(
select
t1.uid,mon
from
(
select
uid,
mon,
row_number() over(
partition by uid
order by
mon desc
) rn
from
(
select
distinct uid,
date_format(start_time, '%Y%m') mon
from
exam_record
) t
) t1
where
rn <= 3
)t1
on t0.uid=t1.uid
and t0.mon=t1.mon
group by t0.uid
having sum(t0.al)=sum(t0.alsub)
order by exam_complete_cnt desc ,t0.uid desc
