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

with t1 as (select date_format(submit_time,'%Y%m') as submit_month,count(1) as month_q_cnt,dayofmonth(last_day(submit_time)) as days_count from practice_record
where year(submit_time) = 2021
group by date_format(submit_time,'%Y%m'),dayofmonth(last_day(submit_time))),

t2 as (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
)

select submit_month,month_q_cnt,round(month_q_cnt/days_count,3) as avg_day_q_cnt from t1
union all
select submit_month,month_q_cnt,avg_day_q_cnt from t2
order by submit_month