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

with t as(
    select 
        exam_id,
        date_format(start_time,"%Y%m") as start_month,
        count(start_time) as month_cnt
    from exam_record
    group by exam_id, date_format(start_time,"%Y%m")
)
select 
    exam_id,
    start_month,
    month_cnt,
    sum(month_cnt) over( partition by exam_id order by start_month asc )
from t

# SELECT DISTINCT
#     exam_id,
#     DATE_FORMAT(start_time,'%Y%m') start_month,
#     count(start_time) over(
#         partition by exam_id,DATE_FORMAT(start_time,'%Y%m')
#     ) month_cnt,
#     count(start_time) over(
#         partition by exam_id
#         order by DATE_FORMAT(start_time,'%Y%m') 
#     ) cum_exam_cnt
# FROM
#     exam_record