题解 | #每份试卷每月作答数和截止当月的作答总数。#

-- 这题还挺简单
-- 1、用group by求解每月作答数，用作临时表t
# select
#     exam_id,
#     replace(date_format(start_time,"%Y-%m"),'-','') 
#         as start_month,
#     count(start_time) as month_cnt
# from exam_record
# group by start_month, exam_id

-- 2、用窗口函数求取截至当月作答数
select
    exam_id,
    start_month,
    month_cnt,
    sum(month_cnt) over(partition by exam_id order by start_month) as cum_exam_cnt
from (select
    exam_id,
    replace(date_format(start_time,"%Y-%m"),'-','') 
        as start_month,
    count(start_time) as month_cnt
from exam_record
group by start_month, exam_id) t