题解 | #平均活跃天数和月活人数#

SELECT
    date_format(submit_time, '%Y%m') submit_month,
    count(*) month_q_cnt,
    round(
        count(*) / dayofmonth(LAST_DAY(submit_time)),
        3
    ) avg_day_q_cnt
FROM
    practice_record
WHERE
    YEAR (submit_time) = 2021
GROUP BY
    submit_month
UNION ALL
    SELECT
        date_format(submit_time, "%Y汇总"),
        count(*),
        round(count(*)/31,3) avg_day_q_cnt
    FROM
        practice_record
    WHERE
        YEAR (submit_time) = 2021
    ORDER BY
        submit_month