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

select device_id,ym
,sum(question_cnt) over(partition by device_id order by ym) as sum_cnt -- 截至当月的每人练题总数
,round(sum(question_cnt) over(partition by device_id order by ym rows 2 preceding)/count(ym) over(partition by device_id order by ym rows 2 preceding),2) as avg3_cnt
,sum(question_cnt)over(order by ym) as total_cnt -- 截至当月所有人的练题总数
from (select device_id,date_format(event_date,'%Y-%m') as ym,
    count(question_id) as question_cnt
from question_practice_detail
group by device_id,ym
) m group by device_id,ym
order by device_id,ym