题解 | 月总刷题数和日均刷题数

-- 2021 每月 + 全年（全年分母固定 31）
SELECT
    stat_month,
    COUNT(*) AS month_q_cnt,
    ROUND(
        COUNT(*) /
        DAY(
            LAST_DAY(
                STR_TO_DATE(CONCAT(stat_month, '01'), '%Y%m%d')
            )
        ),
        3
    ) AS avg_day_q_cnt
FROM (
    SELECT
        DATE_FORMAT(submit_time, '%Y%m') AS stat_month
    FROM practice_record
    WHERE submit_time >= '2021-01-01'
      AND submit_time <  '2022-01-01'
) t
GROUP BY stat_month

UNION ALL

SELECT
    '2021汇总' AS stat_month,
    COUNT(*) AS month_q_cnt,
    ROUND(COUNT(*) / 31, 3) AS avg_day_q_cnt
FROM practice_record
WHERE submit_time >= '2021-01-01'
  AND submit_time <  '2022-01-01'

ORDER BY
    CASE WHEN stat_month = '2021' THEN 2 ELSE 1 END,
    stat_month;