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

感觉自己写的乱七八遭，运行效率还不错…… 另外，牛客网这题目就不能出的没有疑义吗？月均数>=3搞得我以为每个月都要>=3

-- 1，查询出uid,tag,month,完成情况
-- 2，找出月均完成大于等于3的用户
-- 3，统计这些用户的类别情况（包括没完成的）

with tmp as-- 统计每个用户每个月的完成数
(
select uid,mon,sum(compl)as total from
(select uid,tag,month(start_time)as mon,if(submit_time is null,0,1)as compl 
from exam_record 
left join examination_info 
using(exam_id)
 )c
 group by uid,mon
 )
select tag,count(tag)as tag_cnt from examination_info ei
join exam_record er
on er.uid in (select distinct uid from tmp where total>=3) and ei.exam_id=er.exam_id
group by tag
order by tag_cnt desc