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

#大力飞砖写法 避免ONLY_FULL_GROUP_BY COALESCE 
select distinct  t1.submit_month,t1.month_q_cnt,round(t1.month_q_cnt/t2.monthday,3)avg_day_q_cnt
from(
select date_format(submit_time,'%Y%m') as submit_month,count(*) as month_q_cnt
from practice_record
where year(submit_time)='2021'
group by submit_month
)as t1 -- 计算月均
inner join 
(
select date_format(submit_time,'%Y%m') as submit_month,
datediff(DATE_ADD(submit_time,INTERVAL 1 MONTH),submit_time) as monthday
from practice_record -- 计算日均
)as t2 on t1.submit_month=t2.submit_month 
union
(
select '2021汇总',count(*),round(count(*)/31,3)
from practice_record
where year(submit_time)='2021'
)
order by submit_month