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

SELECT a.uid,
    COUNT(DISTINCT date_format(ntime,'%Y-%m')) AS act_month_total,
    COUNT(DISTINCT case when year(ntime) = '2021' then ntime else null end) AS act_days_2021,
    COUNT(DISTINCT CASE WHEN fuzhu = '8' and year(ntime) = '2021' THEN ntime ELSE NULL END) AS act_days_2021_exam,
    COUNT(DISTINCT CASE WHEN fuzhu = '9' and year(ntime) = '2021' THEN ntime ELSE NULL END) AS act_days_2021_question
FROM user_info a
LEFT JOIN (
    SELECT DISTINCT uid, exam_id AS qid, DATE_FORMAT(submit_time, '%Y%m%d') AS ntime, '8' AS fuzhu
    FROM exam_record
    UNION
    SELECT DISTINCT uid, question_id AS qid, DATE_FORMAT(submit_time, '%Y%m%d') AS ntime, '9' AS fuzhu
    FROM practice_record
) x ON a.uid = x.uid
WHERE level > 5
GROUP BY a.uid
order by act_month_total desc,act_days_2021 desc