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

这题目很变态啊，一个月多少天，最后一行年度汇总的格式，以及年度平均（自己测试出来除以的是31），只能说，with大法好！

with tmp as (
select DATE_FORMAT(submit_time,'%Y%m')as submit_month,
count(question_id)as month_q_cnt 
from practice_record 
where year(submit_time)=2021 
group by submit_month order by submit_month)
select submit_month,month_q_cnt,
round(month_q_cnt/datediff(concat(submit_month,'01')+interval 1 month,concat(submit_month,'01')),3)as avg_day_q_cnt 
from tmp 
union (
select '2021汇总' as submit_month,sum(month_q_cnt)as month_q_cnt,round(sum(month_q_cnt)/31,3) as avg_day_q_cnt from tmp);