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

SELECT i.tag, COUNT(r.start_time) AS tag_cnt
FROM exam_record AS r LEFT JOIN examination_info AS i ON r.exam_id=i.exam_id
WHERE r.uid IN(
            SELECT a.uid
            FROM(
                SELECT DATE_FORMAT(start_time, '%Y%m' ) AS month, 
                    uid, 
                    COUNT(submit_time) AS com_cnt 
                FROM exam_record
                WHERE submit_time IS NOT NULL
                GROUP BY DATE_FORMAT(start_time, '%Y%m' ) , uid
                ) AS a
            WHERE a.com_cnt >=3
            )
GROUP BY i.tag
ORDER BY tag_cnt DESC;