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

思路：使用一维表的思维，将试卷和答题结合起来，且区分来源，这样既可以方便计算总量，也方便根据类别计算。

select
    uid,
    count(distinct act_month) as act_month_total,
    count(distinct case when year(act_days) = '2021' then act_days end) as act_days_2021,
    count(distinct case when year(act_days) = '2021' and tag = 'exam' then act_days end) as act_days_2021_exa,
    count(distinct case when year(act_days) = '2021' and tag = 'question' then act_days end) as act_days_2021_questi
from(
    select uid
    from user_info
    where `level` = 6 or `level` = 7
    ) as t0
left join(
        select 
            uid, 
            date_format(start_time, '%Y%m') as act_month,
            date_format(start_time, '%Y%m%d') as act_days,
            'exam' as tag
        from exam_record
        union
        select
            uid,
            date_format(submit_time, '%Y%m') as act_month,
            date_format(submit_time, '%Y%m%d') as act_days,
            'question' as tag
        from practice_record
        ) as t1
using (uid)
group by uid
order by act_month_total desc, act_days_2021 desc

这个题由于是几个月前单独做过，忘记了下面的是自己写的还是评论区里趴的了，下面的这个思路更符合实际工作中的撰写方式，根据需要增删字段，理解和修改起来都简单很多。

-- 一个问题，试卷活跃最好以start_time为基准（结果能通过）
select 
    uid, 
    if(act_month_total is null, 0, act_month_total), 
    if(act_days_2021 is null, 0, act_days_2021), 
    if(act_days_2021_exa is null, 0, act_days_2021_exa), 
    if(act_days_2021_questi is null, 0, act_days_2021_questi)
from(
    select uid
    from user_info
    where `level` = 6 or `level` = 7
    ) as t0
left join(
        -- 总活跃月数
        select uid, count(distinct date_format(submit_time, '%Y%m')) as act_month_total 
        from(
            -- id为主键，重复是否会报错，是否要指定必要列合并(列不同无法合并)
            select uid, submit_time
            from exam_record 
            union
            select uid, submit_time
            from practice_record
            ) as t1
        group by uid
        ) as t1
using(uid)
left join(
        -- 2021年活跃天数
        select uid, count(distinct date_format(submit_time, '%Y%m%d')) as act_days_2021
        from(
            select uid, submit_time
            from exam_record 
            union
            select uid, submit_time
            from practice_record
            ) as t1
        where year(submit_time) = '2021'
        group by uid
        ) as t2
using(uid)
left join(
        -- 2021年试卷作答活跃天数
        select uid, count(distinct date_format(start_time, '%Y%m%d')) as act_days_2021_exa
        from exam_record
        where year(start_time) = '2021'
        group by uid
        ) as t3
using(uid)
left join(
        -- 2021年答题活跃天数
        select uid, count(distinct date_format(submit_time, '%Y%m%d')) as act_days_2021_questi
        from practice_record
        where year(submit_time) = '2021'
        group by uid
        ) as t4
using(uid)
order by act_month_total desc, act_days_2021 desc