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

with t as(
select distinct uid from(
SELECT
	DATE_FORMAT( start_time, '%Y-%m' ) AS dt,
	uid,
	count( CASE WHEN DATE_FORMAT(start_time,'%Y-%m' )= DATE_FORMAT( submit_time,'%Y-%m' ) THEN exam_id end) AS cnt 
	FROM
		exam_record
GROUP BY
	1,2
having cnt >=3) tmp )

select tag,count(exam_id) as tag_cnt from t left join exam_record using(uid) left join examination_info using(exam_id) group by 1 order by 2 desc