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

select *
from (
    (
        select 
            submit_month,
            month_q_cnt,
            round(month_q_cnt/month_cnt,3) as avg_day_q_cnt
        from (
            select
                date_format(submit_time, '%Y%m') as submit_month,
                day(last_day(submit_time)) as month_cnt,
                count(submit_time) as month_q_cnt
            from practice_record
            where year(submit_time) = 2021 
            group by date_format(submit_time, '%Y%m'), day(last_day(submit_time))
        )as t1
    )
    union all 
    (
        select
            '2021汇总' as submit_month,
            count(submit_time) as month_q_cnt,
            round(count(submit_time)/31,3) as avg_day_q_cnt
        from practice_record
        where year(submit_time) = 2021
    )
)as t2
order by submit_month asc

获取当月天数：day(last_day(submit_time))