题解 | #平均活跃天数和月活人数#

select i2.tag,count(*) as tag_cnt
from
(
 select distinct a.uid
   from
    (select uid,date_format(e.submit_time,'%Y%m') as month
    ,count(e.submit_time) as cnt
     from exam_record e 
     left join examination_info i
     on e.exam_id = i.exam_id
     group by 1,2
     having count(e.submit_time) >= 3
    )a #首先找出月均答题数超过3次的用户
)a1  #注意！再将uid提出来，避免后续连接产生多重数据！
join exam_record e2 
On a1.uid = e2.uid
join examination_info i2
on e2.exam_id=i2.exam_id
group by i2.tag
order by count(*) desc
#接着，找出以上用户的作答卷子tag，并group求出