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

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