-- 2020算在整体数据中，计算2021 活跃度才计算
-- 不能使用 Year(DATE_FORMAT(now(),'%Y-%m'))=2021  变成字符串
-- 字符串转日期
str_to_date（'2022-03-02','%Y-%m-%d'）
DATE(cast（时间 as datetime）)


-- 日期转字符串
date_format(日期,'%Y-%m-%d')
time_format(时间,'%H-%i-%s')


select uid1,
count(distinct start_month) act_month_total,
count(distinct case when left(start_month,4)='2021' then start_days end) act_days_2021,
 count(distinct case when tag='exam' and left(start_month,4)='2021' then start_days end)  act_days_2021_exam,
 count(distinct case when tag='question'  and left(start_month,4)='2021' then start_days end)  act_days_2021_question
from(
            select b.uid uid1,start_month,start_days,tag
                    from 
                        (select uid
                        from user_info
                        where level in (6,7)
                        ) b
                left join     
            
                (
                        select distinct uid,DATE_FORMAT(start_time,'%Y-%m') start_month,
                        DATE_FORMAT(start_time,'%Y-%m-%d') start_days,'exam' tag
                        from exam_record
                        
                        UNION ALL
                        select distinct uid,DATE_FORMAT(submit_time,'%Y-%m') start_month,
                        DATE_FORMAT(submit_time,'%Y-%m-%d') start_days,'question' tag
                        from practice_record
                
                ) a
                on b.uid=a.uid
        
        ) t
group by uid1
order by act_month_total desc,act_days_2021 desc