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

with t as(
    select 
    date_format(submit_time,"%Y%m") as ym,
    date_format(submit_time,"%Y%m%d") as ymd,
    DAY(LAST_DAY(submit_time)) AS days_in_month
    from practice_record
    where year(submit_time)='2021'
)
,t1 as(
    select 
    ym as submit_month,
    count(ymd) as month_q_cnt,
    round(count(ymd)/max(days_in_month),3) as avg_day_q_cnt
    from t
    group by ym
    order by ym
)
,t2 as(
    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 t1
)

select * from t1 
union all 
select * from t2