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

with
    levellist as (
        select
            uid
        from
            user_info
        where
            level in (6, 7)
    )
select
    uid,
    count(distinct date_format(submit_time,"%YY-%mm")) as act_month_total,
    count(distinct if(year(submit_time) = 2021 and day(submit_time) is not null ,date_format    (submit_time,"%YY-%mm-%dd"),null)) as act_days_2021,
    count(distinct if(year(submit_time) = 2021 and day(submit_time) is not null and tag = "exam",date_format(submit_time,"%YY-%mm-%dd"),null)) as act_days_2021_exam,
    count(distinct if(year(submit_time) = 2021 and day(submit_time) is not null and tag = "practice",date_format(submit_time,"%YY-%mm-%dd"),null)) as act_days_2021_question
from(
    select uid, submit_time, tag from 
    levellist left join (
        select
            uid,
            start_time as submit_time,
            'exam' as tag
        from
            exam_record
        where
            uid in (
                select
                    *
                from
                    levellist
            )
    
union

    select
        uid,
        submit_time,
        'practice' as tag
    from
        practice_record
    where
        uid in (
            select
                *
            from
                levellist
        ) 
) as combinedlist
using(uid)
) as fianllist
group by 
uid
order by
act_month_total desc,
act_days_2021 desc

本题很烦，第一点先求出符合level在6-7的uid列表with的临时表，然后与practice_record和exam_record根据tag的不同进行一个匹配，然后与临时表进行一个左连接，这样就能求出所有uid下的所有时间