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

select
    submit_month,
    month_q_cnt,
    round(month_q_cnt / 天数, 3) avg_day_q_cnt
from
    (
        select
            date_format (submit_time, '%Y%m') submit_month,
            count(id) month_q_cnt
        from
            test.practice_record
        where
            year (submit_time) = 2021
        group by
            date_format (submit_time, '%Y%m')
    ) a
    join (
        select distinct
            date_format (submit_time, '%Y%m') month,
            day (last_day (submit_time)) 天数
        from
            test.practice_record
        where
            year (submit_time) = 2021
    ) b on a.submit_month = b.month
union
select
    '2021汇总' submit_month,
    count(id) month_q_cnt,
    round(count(id) / 31, 3) avg_day_q_cnt
from
    test.practice_record
where
    year (submit_time) = 2021
order by
    submit_month asc