Rolling 12-Month Revenue per Month

SQL coding challenge · Difficulty: medium · +200 XP

Problem

Show a 12-month (12-row) rolling revenue sum per month. With this sample the window covers every month up to the current one.

Tables

Table: monthly_revenue

| month_start | revenue |
| --- | --- |
| 2024-01-01 | 500 |
| 2024-02-01 | 700 |
| 2024-03-01 | 600 |
| 2024-04-01 | 900 |
| 2024-05-01 | 800 |
| 2024-06-01 | 1000 |

Expected Output

| month_start | revenue | rolling_12month_revenue |
| --- | --- | --- |
| 2024-01-01 | 500 | 500 |
| 2024-02-01 | 700 | 1200 |
| 2024-03-01 | 600 | 1800 |
| 2024-04-01 | 900 | 2700 |
| 2024-05-01 | 800 | 3500 |
| 2024-06-01 | 1000 | 4500 |

• Return: month_start, revenue, rolling_12month_revenue
• Sort by month_start ascending
• Function to use: SUM() OVER (ORDER BY month_start ROWS BETWEEN 11 PRECEDING AND CURRENT ROW)