题解 | #分别满足两个活动的人#

这个题有个地方有些模糊，那就是怎么定义活跃。对于试卷作答用户来讲，取start_time字段（不管是否提交都算活跃）；对于答题用户来讲，取submit_time字段即可。本题作答巧妙之处在于构建了一列，用于判断2021年试卷和答题用户。

-- 先构造一个临时表
with tmp as (
select 
    a.uid,
    b.exam_id as id,
    b.start_time as tm,
    concat(year(b.start_time), 'shijuan') as tp
from (select uid from user_info where level between 6 and 7) a 
left join exam_record b on a.uid=b.uid
union all
select 
    c.uid,
    d.question_id as id,
    d.submit_time as tm,
    concat(year(d.submit_time), 'dati') as tp
from (select uid from user_info where level between 6 and 7) c
left join practice_record d on c.uid=d.uid)

-- 然后进行查询
select 
    uid, 
    count(distinct date_format(tm, '%Y%m')) as act_month_total,
    count(distinct if(year(tm)=2021, date(tm), null)) as act_days_2021,
    count(distinct if(tp='2021shijuan', date(tm), null)) as act_days_2021_exam,
    count(distinct if(tp='2021dati', date(tm), null)) as act_days_2021_question
from tmp 
group by uid
order by act_month_total desc, act_days_2021 desc