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

WITH
### 每个月用户的
    monthly_practices_u AS(
        SELECT device_id,DATE_format(event_date,'%Y-%m') ym,COUNT(question_id) m_user_cnt
        FROM question_practice_detail
        GROUP BY DATE_format(event_date,'%Y-%m'),device_id
    ),
### 每个月总的
    monthly_practices AS(
        SELECT DATE_format(event_date,'%Y-%m') ym,COUNT(question_id) m_cnt
        FROM question_practice_detail
        GROUP BY DATE_format(event_date,'%Y-%m')
    ),
### 每个月用户累计的
    monthly_practices_u_sum AS(
        SELECT device_id,ym,SUM(m_user_cnt) OVER(partition by device_id order by ym) sum_cnt
        FROM monthly_practices_u
    ),
### 每个月全局累计的
    monthly_practices_sum AS(
        SELECT ym,SUM(m_cnt) OVER(order by ym) total_cnt
        FROM monthly_practices
    ),
### 每三个月平均的
    3month_practices_avg AS(
    SELECT 
        device_id,
        ym,
        ROUND(AVG(m_user_cnt) OVER(partition by device_id order by ym ROWS BETWEEN 2 PRECEDING AND CURRENT ROW),2) avg3_cnt
    FROM monthly_practices_u
    )

SELECT t1.device_id,t1.ym,t1.sum_cnt,t3.avg3_cnt,t2.total_cnt
FROM monthly_practices_u_sum t1
INNER JOIN monthly_practices_sum t2
ON t1.ym= t2.ym
INNER JOIN 3month_practices_avg t3
ON t1.device_id = t3.device_id
AND t1.ym = t3.ym
ORDER BY t1.device_id,t1.ym