题解 | #截至当月的练题情况#

with cte as (
select device_id,left(event_date,7) as ym ,count(*) as cnt
from question_practice_detail
group by device_id,ym )
select device_id,ym,
               sum(cnt)over(partition by device_id order by ym) as sum_cnt,
               round(avg(cnt)over(partition by device_id order by ym rows between 2 preceding and current row ),2) as avg3_cnt,
               sum(cnt)over(order by ym) as total_cnt
from cte
order by device_id ,ym