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

select tag,count(exr.uid) as tag_cnt from exam_record as exr
inner join
(select distinct q.uid as uid from
(select uid,count(uid) over(partition by uid) as sub_num from exam_record as er
where month(submit_time)=9) as q
where sub_num>=3) as q1
on exr.uid=q1.uid
left join examination_info as ei
on exr.exam_id=ei.exam_id
group by tag
order by tag_cnt desc
;

思路：

1.筛选9月答题的用户

2.使用开窗函数统计每位用户的9月答题数

3.筛选出9月答题数大于3的用户

4.答题记录表（exam_record）中筛选出9月答题数大于3的用户

5.按照试卷类别分组，统计，排序