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

with t1 as(select distinct DATE_FORMAT(submit_time,'%Y-%m') as dt,a.uid from exam_record a left join user_info u on a.uid=u.uid where level in (6,7) having dt is not null 
union
select distinct DATE_FORMAT(submit_time,'%Y-%m') as dt,a.uid from practice_record a left join user_info u on a.uid=u.uid where level in (6,7) having dt is not null order by 2),
a1 as (
select u.uid,count(dt) as act_month_total from user_info u left join t1 on u.uid=t1.uid where level in (6,7) group by 1)
,

t2 as(select distinct DATE_FORMAT(submit_time,'%Y-%m-%d') as dt,a.uid from exam_record a left join user_info u on a.uid=u.uid where level in (6,7) and year(submit_time)=2021 having dt is not null 
union
select distinct DATE_FORMAT(submit_time,'%Y-%m-%d') as dt,a.uid from practice_record a left join user_info u on a.uid=u.uid where level in (6,7) and year(submit_time)=2021 having dt is not null order by 2),
a2 as (
select u.uid,count(dt) as act_days_2021	 from user_info u left join t2 on u.uid=t2.uid where level in (6,7) group by 1)
,

t3 as (
select distinct DATE_FORMAT(submit_time,'%Y-%m-%d') as dt,a.uid from exam_record a left join user_info u on a.uid=u.uid where level in (6,7) and year(submit_time)=2021 having dt is not null ),
a3 as (
select u.uid,count(dt) as act_days_2021_exam from user_info u left join t3 on u.uid=t3.uid where level in (6,7) group by 1)
,

t4 as (
select distinct DATE_FORMAT(submit_time,'%Y-%m-%d') as dt,a.uid from practice_record a left join user_info u on a.uid=u.uid where level in (6,7) and year(submit_time)=2021 having dt is not null ),
a4 as(
select u.uid,count(dt) as act_days_2021_question from user_info u left join t4 on u.uid=t4.uid where level in (6,7) group by 1)

select a1.uid,act_month_total,act_days_2021,act_days_2021_exam,act_days_2021_question from a1 join a2 on a1.uid=a2.uid join a3 on a1.uid=a3.uid join a4 on a1.uid=a4.uid order by 2 desc,3 desc