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

select t1.submit_time,count(*) as month_q_cnt,
round(count(*)/t1.cnt_day,3) as avg_day_q_cnt 
from 
(select uid,question_id,date_format(submit_time,'%Y%m') as submit_time,
(case when month(submit_time) in (1,3,5,7,8,10,12) then 31 
 when month(submit_time)=2 then 28 else 30 end) as cnt_day from 
 practice_record as p1 where year(p1.submit_time)=2021) t1
 group by t1.submit_time,t1.cnt_day 
 union 
select '2021汇总' as submit_time,count(*) as month_q_cnt,
round(count(*)/ 31,3) as avg_day_q_cnt 
from practice_record as p1 where year(p1.submit_time)=2021 
order by submit_time