明确题意：

统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序。

问题拆解：

  • 本题主要是考察知识点：join、case when 、group by、date_format等
  • DATE_FORMAT(submit_time, "%Y-%m-%d" ) 返回的是2021-09-01这样；
  • 先查出level=6，7的用户，得到表t1
  • t2,t3,t4,t5分别对应用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数
  • 要用left join关联，有些用户没有作答某个题目时，记录也要保留！
  • 最后的结果有NULL时需要用case when 处理一下

代码实现： 

select 
t1.uid,
case when t2.act_month_total is null then 0 else  t2.act_month_total end as act_month_total,
case when t3.act_days_2021 is null then 0 else  t3.act_days_2021 end as act_days_2021,
case when t4.act_days_2021_exam is null then 0 else  t4.act_days_2021_exam end as act_days_2021_exam,
case when t5.act_days_2021_question is null then 0 else  t5.act_days_2021_question end as act_days_2021_question
from (
select * from user_info where level in (6,7)
    )t1 
 -- join 不能用join
 left join 
 (
    select uid ,count(*) as act_month_total
    from (
            select
            uid, DATE_FORMAT(act_time , '%Y-%m')
            from (
                select uid,'exam' as type,start_time as act_time 
                    from exam_record
                 union all  
                select uid,'practice' as type,submit_time as act_time 
                    from practice_record
            )t00
            group by uid, DATE_FORMAT(act_time , '%Y-%m')
        )t01 group by uid
)t2 
on t1.uid = t2.uid
 -- join 不能用join
 left join  (
    select uid ,count(*) as act_days_2021
    from (
            select
            uid, DATE_FORMAT(act_time , '%Y-%m-%d')
            from (
                select uid,'exam' as type,start_time as act_time 
                    from exam_record
                 union all  
                select uid,'practice' as type,submit_time as act_time 
                    from practice_record
            )t00 where year(act_time)  = '2021'
            group by uid, DATE_FORMAT(act_time , '%Y-%m-%d')
        )t01 group by uid
)t3
on t1.uid = t3.uid
 -- join 不能用join
 left join  (
        select uid ,count(*) as act_days_2021_exam
    from (
            select
            uid, DATE_FORMAT(act_time , '%Y-%m-%d')
            from (
                select uid,'exam' as type,start_time as act_time 
                    from exam_record
            )t00 where year(act_time)  = '2021'
            group by uid, DATE_FORMAT(act_time , '%Y-%m-%d')
        )t01 group by uid
)t4
on t1.uid = t4.uid
 -- join 不能用join
 left join  (
     select uid ,count(*) as act_days_2021_question
    from (
            select
            uid, DATE_FORMAT(act_time , '%Y-%m-%d')
            from (
              select uid,'practice' as type,submit_time as act_time 
                    from practice_record
            )t00 where year(act_time)  = '2021'
            group by uid, DATE_FORMAT(act_time , '%Y-%m-%d')
        )t01 group by uid
)t5
on t1.uid = t5.uid
order by 
t2.act_month_total desc ,
t3.act_days_2021 desc ;

为啥不能用join？如下，t2不含有1003，但1003的记录要保留下来，算作是0！！！ 
mysql>  select uid ,count(*) as act_month_total
    ->     from (
    ->             select
    ->             uid, DATE_FORMAT(act_time , '%Y-%m-%d')
    ->             from (
    ->                 select uid,'exam' as type,start_time as act_time 
    ->                     from exam_record
    ->                  union all  
    ->                 select uid,'practice' as type,submit_time as act_time 
    ->                     from practice_record
    ->             )t00
    ->             group by uid, DATE_FORMAT(act_time , '%Y-%m-%d')
    ->         )t01 group by uid
    -> ;
+------+-----------------+
| uid  | act_month_total |
+------+-----------------+
| 1001 |               2 |
| 1005 |               1 |
| 1006 |               5 |
| 1002 |               1 |
| 1004 |               1 |
+------+-----------------+
5 rows in set (0.00 sec)

不足之处，欢迎指正。