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

疯狂叠if，参考了最热门解答大佬的分组方式

WITH niuuser_info AS
(SELECT uid FROM user_info
WHERE level=6 OR level=7)

SELECT ni.uid,COUNT(DISTINCT DATE_FORMAT(active_record.time,'%Y%m')) AS act_month_total,
COUNT(DISTINCT if(DATE_FORMAT(active_record.time,'%Y')=2021,
                  DATE_FORMAT(active_record.time,'%Y%m%d'),null)) 
                  AS act_days_2021,
COUNT(DISTINCT if(DATE_FORMAT(active_record.time,'%Y')=2021 AND tag='exam',
                  DATE_FORMAT(active_record.time,'%Y%m%d'),null))
      AS act_days_2021_exam,
COUNT(DISTINCT if(DATE_FORMAT(active_record.time,'%Y')=2021 AND tag='practice',
                  DATE_FORMAT(active_record.time,'%Y%m%d'),null)) AS act_days_2021_question
FROM niuuser_info ni
LEFT JOIN (SELECT uid,exam_id,start_time AS time,'exam' AS tag
           FROM exam_record
           UNION ALL
           SELECT uid,question_id AS exam_id,submit_time AS time,'practice' AS tag
           FROM practice_record) AS active_record
ON ni.uid=active_record.uid 
GROUP BY ni.uid
ORDER BY act_month_total DESC,act_days_2021 DESC