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

with user as(select uid
              from user_info
              where level=6 or level=7) 
select uid,count(distinct YEAR(start_time),month(start_time)) as act_month_total,
                     count(distinct act_days_2021) as act_days_2021,
           count(distinct act_days_2021_exam)as act_days_2021_exam,
                     count(distinct act_days_2021_question) as act_days_2021_question
from 

            (select *,(CASE WHEN YEAR(start_time)=2021 THEN DATE(start_time) END) AS act_days_2021,
                             (CASE WHEN YEAR(start_time)=2021 AND type='exam' THEN DATE(start_time) END) AS act_days_2021_exam,
                             (CASE WHEN YEAR(start_time)=2021 AND type='practice' THEN DATE(start_time) END) AS act_days_2021_question
             FROM (select user.uid,er.start_time,'exam' as type
                        from user left join exam_record er ON user.uid=er.uid
                        union 
                        select user.uid,pr.submit_time,'practice' as type
                        from user left join practice_record pr ON user.uid=pr.uid)tt)tt
GROUP BY uid