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

/*
1、表拼接，exam_record left join examination_info using(exam_id)
2、group by uid，计算月均完成数，having 大于等于3
3、1和2作为一个子表，返回符合条件的uid
4、用IN在exam_record中统计

*/
/*
with tmp as(
    select uid, exam_id, tag, start_time, submit_time, score
    from exam_record left join examination_info using(exam_id)
)

select
    tag,
    count(tag) as tag_cnt
from tmp
where uid in 
    (select uid
    from tmp
    group by uid, month(start_time)
    having count(submit_time)>=3)
group by tag
order by tag_cnt desc
*/

/*优化*/
/*先查“当月均完成试卷数”不小于3的用户id，将结果作为子表，再需要联表进行查询*/
select tag, count(tag) as tag_cnt
from exam_record left join examination_info using(exam_id)
where uid in 
    (select uid from exam_record group by uid, month(start_time) having count(submit_time)>=3)
group by tag
order by tag_cnt desc