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

WITH uid AS(
	SELECT uid
	FROM exam_record
	WHERE submit_time IS NOT NULL
	GROUP BY uid
	HAVING COUNT(1)/COUNT(DISTINCT DATE_FORMAT(submit_time,'%Y%m'))>=3
)
SELECT i.tag,COUNT(1) tag_cnt
FROM exam_record r JOIN examination_info i
ON r.exam_id=i.exam_id JOIN uid u
ON r.uid=u.uid
GROUP BY i.tag
ORDER BY tag_cnt DESC