题解 | #每个6/7级用户活跃情况#

提供Mysql下一种相当粗暴的解法……看了一下好像还没有人发过

select user_info.uid, ifnull(count(distinct month),0) a1,
       ifnull(count(distinct day),0) a2,
       ifnull(count(distinct examday),0),
       ifnull(count(distinct questionday),0)
from (
    select uid, date_format(a.start_time,'%Y%m') month,
           if(year(start_time)=2021,date_format(a.start_time,'%Y%m%d'),null) day,
           if(exam_id like '9%' and year(start_time)=2021,date_format(a.start_time,'%Y%m%d'),null) examday,
           if(exam_id like '8%' and year(start_time)=2021,date_format(a.start_time,'%Y%m%d'),null) questionday
    from (select uid,exam_id,start_time from exam_record union
          select uid,question_id,submit_time from practice_record) a) b
right join user_info on user_info.uid = b.uid
where level between 6 and 7
group by uid
order by a1 desc, a2 desc