题解 | #月均完成试卷数不小于3的用户爱作答的类别#

with table1 as(
    select uid,start_time,submit_time,tag
    from exam_record left join examination_info using(exam_id)
),
table2 as(
    select uid 
    from table1
    where submit_time is not null 
    group by uid,date_format(submit_time,'%Y%m')
    having count(submit_time) >= 3
)
select tag, count(start_time) as tag_cnt 
from table1
where uid in (select uid from table2)
group by tag
order by tag_cnt desc