题解 | #月均完成试卷数不小于3的用户爱作答的类别#
月均完成试卷数不小于3的用户爱作答的类别
https://www.nowcoder.com/practice/b1d13efcfa0c4ecea517afbdb9090845
select
ei.tag tag,
count(tag) tag_cnt
from
exam_record er
join examination_info ei on er.exam_id = ei.exam_id
where
uid in (
SELECT
uid
FROM
exam_record
WHERE
submit_time IS NOT NULL
GROUP BY
DATE_FORMAT (submit_time, '%Y%m'),
uid
HAVING
count(submit_time) / count(distinct DATE_FORMAT (start_time, "%Y%m")) >= 3
)
group by
ei.tag
order by
tag_cnt desc;
