SQL129 月均完成试卷数不小于3的用户爱作答的类别

题目描述

现有试卷作答记录表exam_record（uid：用户ID, exam_id：试卷ID, start_time：开始作答时间, submit_time：交卷时间，没提交的话为NULL, score：得分），示例数据如下：

id	uid	exam_id	start_time	submit_time	score
1	1001	9001	2021-07-02 09:01:01	(NULL)	(NULL)
2	1002	9003	2021-09-01 12:01:01	2021-09-01 12:21:01	60
3	1002	9002	2021-09-02 12:01:01	2021-09-02 12:31:01	70
4	1002	9001	2021-09-05 19:01:01	2021-09-05 19:40:01	81
5	1002	9002	2021-07-06 12:01:01	(NULL)	(NULL)
6	1003	9003	2021-09-07 10:01:01	2021-09-07 10:31:01	86
7	1003	9003	2021-09-08 12:01:01	2021-09-08 12:11:01	40
8	1003	9001	2021-09-08 13:01:01	(NULL)	(NULL)
9	1003	9002	2021-09-08 14:01:01	(NULL)	(NULL)
10	1003	9003	2021-09-08 15:01:01	(NULL)	(NULL)
11	1005	9001	2021-09-01 12:01:01	2021-09-01 12:31:01	88
12	1005	9002	2021-09-01 12:01:01	2021-09-01 12:31:01	88
13	1005	9002	2021-09-02 12:11:01	2021-09-02 12:31:01	89

试卷信息表examination_info（exam_id：试卷ID, tag：试卷类别, difficulty：试卷难度, duration：考试时长, release_time：发布时间），示例数据如下：

id	exam_id	tag	difficulty	duration	release_time
1	9001	SQL	hard	60	2020-01-01 10:00:00
2	9002	C++	easy	60	2020-02-01 10:00:00
3	9003	算法	medium	80	2020-08-02 10:00:00

请从表中统计出 “当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出，示例输出如下：

tag	tag_cnt
C++	4
SQL	2
算法	1

解释：用户1002和1005在2021年09月的完成试卷数目均为3，其他用户均小于3；然后用户1002和1005作答过的试卷tag分布结果按作答次数降序排序依次为C++、SQL、算法。

思路

题目要求：请从表中统计出 “当月均完成试卷数”不小于3的用户们爱作答的类别及作答次数，按次数降序输出。

先用  group by uid 按照用户进行分组，分组后求：

count(submit_time)：某用户总的完成试卷的次数

count(distinct date_format(submit_time,'%Y%m')：某用户参与做答所在月的个数，由于该用户在同一个月可能作答多次，所以需要去重

所以月均完成试卷数不小于3：count(submit_time)/count(distinct date_format(submit_time,'%Y%m')>=3

代码

select tag,count(*) as tag_cnt
from  exam_record a
join examination_info b
on a.exam_id=b.exam_id	
where uid in (select uid
                from exam_record
                group by uid	
                Having count(submit_time)/count(distinct date_format(submit_time,'%Y%m'))>=3)
group by tag
order by tag_cnt desc;

结果