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

思路：先找出-- “当月均完成试卷数”不小于3的用户----表t2

然后在 ‘连接表’ 中统计 符合要求的用户 的 作答类别及 作答次数。

耗时1h www

with t2 as

(

select  date_format(start_time,"%m") as dt,

        any_value(uid) as uid,

        any_value(tag) as tag,

        count(submit_time) as cnum

from exam_record t left join examination_info t1 on t.exam_id=t1.exam_id

group by date_format(start_time,"%m"),uid

having count(submit_time)>=3

)

select tag,count(*) as tag_cnt

from exam_record t left join examination_info t1 on t.exam_id=t1.exam_id

where uid in (select uid from t2)

group by tag

order by tag_cnt desc