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

select e2.tag as tag,count(*) as tag_cnt
from exam_record e1 
right join examination_info e2
on e1.exam_id=e2.exam_id
where e1.uid in 
(
    select uid
    from exam_record
    where submit_time is not null
    group by uid 
    having count(*)/count(distinct month(start_time))>=3
) 
group by e1.exam_id
order by tag_cnt desc








#第一步：筛选出月均完成大于3的用户，月均完成数目=总共完成的数目/月份的数目


select e2.tag as tag,count(*) as tag_cnt
from exam_record e1 
right join examination_info e2
on e1.exam_id=e2.exam_id
where e1.uid in 
(
    select uid
    from exam_record
    where submit_time is not null
    group by uid 
    having count(*)/count(distinct month(start_time))>=3
) 
group by e1.exam_id
order by tag_cnt desc

##
##
# SELECT ei.tag,count(er.uid) tag_cnt from exam_record er
# LEFT JOIN examination_info ei
# on er.exam_id=ei.exam_id
# WHERE uid in
# (    
#     select t.uid FROM
#     (
#         select uid,DATE_FORMAT(submit_time,'%Y-%m') month 
#         from exam_record
#         where submit_time is not null
#     ) t
#      group by t.uid
#      having count(t.uid)/count(DISTINCT t.`month`)>=3
# )
# group by ei.tag
# order by tag_cnt desc