Window function with a ROWS BETWEEN frame.
For each month (YYYY-MM), return total monthly revenue AND a 3-month trailing average (current month plus the 2 prior; at the left edge the average just uses what's available). Columns: `month`, `monthly_revenue`, `rolling_3mo_avg`. Round the average to 2 decimals. Order by month. Table: `ci_orders`.
| Column | Type | Key |
|---|---|---|
| order_id | INT | PK |
| customer_id | INT | |
| product_name | TEXT | |
| category | TEXT | |
| order_date | DATE | |
| revenue | NUMERIC |
Each hint you reveal reduces the XP you can earn. Try the query first.
Bucket with DATE_TRUNC('month', order_date) in a CTE.
AVG(monthly_revenue) OVER (ORDER BY month ROWS BETWEEN 2 PRECEDING AND CURRENT ROW).
ROUND(..., 2) to match the expected decimal format.