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

select
    date_format (submit_time, '%Y%m') as submit_month,
    count(1) as month_q_cnt,
    round(count(1)  / (
        case
            when max(month (submit_time)) in (1, 3, 5, 7, 8, 10, 12) then 31
            when max(month (submit_time))= 2 then 28
            else 30
        end
    ),3)as avg_day_q_cnt
from
    practice_record
where
    year (submit_time) = 2021
group by
    date_format (submit_time, '%Y%m')
union all
select
    '2021汇总' as submit_month,
    count(1) as month_q_cnt,
    round(count(1) / 31, 3) as avg_day_q_cnt
from
    practice_record
where
    year (submit_time) = 2021
order by
    submit_month;