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

select
    submit_month,
    cnt month_q_cnt,
    format (
        cnt / DAY (
            LAST_DAY (cast(concat (submit_month, '01') as date))
        ),
        3
    ) avg_day_q_cnt
from
    (
        select
            date_format (submit_time, '%Y%m') submit_month,
            count(id) cnt
        from
            practice_record
        where
            date_format (submit_time, '%Y') = '2021'
        group by
            date_format (submit_time, '%Y%m')
    ) a
    
union all
select
    '2021汇总' submit_month,
    cnt month_q_cnt,
    format (cnt / 31, 3) avg_day_q_cnt
from
    (
        select
            date_format (submit_time, '%Y') submit_month,
            count(id) cnt
        from
            practice_record
        where
            date_format (submit_time, '%Y') = '2021'
        group by
            date_format (submit_time, '%Y')
    ) a
order by submit_month