#思路:整体思路其实很简单,就是把要求的数分别求出来,在进行相连即可,就是代码长。
#另外:由于活跃的月份数和天数不分试卷还是联系,所以先把试卷表和练习表union all形成表tb1备用。
with tb1 as
(select uid, start_time as a, 1 as b from exam_record
union all 
select uid, submit_time as a, 2 as b from practice_record)

select uid, if(aa is null,0,aa) aa, if(bb is null,0,bb) bb, if(cc is null,0,cc) cc, if(dd is null,0,dd) dd
from

#求活跃月份
(select uid, count(distinct date_format(a,'%Y%m')) aa
from user_info left join tb1 using(uid) 
where user_info.level in (6,7)
group by uid) as tb2
left join

#求2021年活跃天数
(select uid, count(distinct date(a)) bb
from user_info left join tb1 using(uid) 
where user_info.level in (6,7) and year(a)=2021
group by uid) as tb3 using(uid)
left join

#求2021年试卷作答天数
(select uid, count(distinct date(start_time)) cc
from user_info left join exam_record using(uid) 
where user_info.level in (6,7) and year(start_time)=2021
group by uid) as tb4 using(uid)
left join

#求2021年练习太天数
(select uid, count(distinct date(submit_time)) dd
from user_info left join practice_record using(uid) 
where user_info.level in (6,7) and year(submit_time)=2021
group by uid) as tb5 using(uid)
order by aa desc, bb desc