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

select tag,count(1) tag_cnt
from 
    (select ei.exam_id,ei.tag,er.uid,er.start_time,er.submit_time,er.score
    from exam_record er 
    left join examination_info ei using(exam_id)
    where  uid in 
    (select distinct uid
    from 
        (select tag,uid,start_time,submit_time,score,count(1) over (partition by uid,ms) rn
        from 
            (select ei.exam_id,ei.tag,er.uid,er.start_time,er.submit_time,er.score,month    (start_time) ms
            from exam_record er 
            left join examination_info ei using(exam_id)
            where submit_time is not null and score is not null)t1)t1
    where rn >=3))t1
    group by 1
order by tag_cnt desc