Task
You are asked to build a rolling monthly forecast for Zulily-style demand planning using historical order data. Write a PostgreSQL query that calculates a 3-month rolling forecast for each product category based on prior monthly sales trends and seasonality, then outputs the forecast for the next month after each observed month.
Use the provided transaction history to derive monthly revenue, compare each month to the same month in the prior year when available, and smooth the forecast with a 3-month moving average. The result should show the forecasted month, category, historical baseline, seasonality factor, and final forecast value.
Schema
| Table | Column | Type | Description |
|---|
orders | order_id | INT | Primary key for each order |
orders | order_date | DATE | Date the order was placed |
orders | category | VARCHAR(50) | Merchandise category |
orders | net_sales | NUMERIC(12,2) | Net revenue from the order |
orders | channel | VARCHAR(20) | Sales channel, such as app or web |
Sample data
| order_id | order_date | category | net_sales | channel |
|---|
| 1 | 2023-01-15 | Apparel | 120.00 | web |
| 2 | 2023-02-10 | Apparel | 180.00 | app |
| 3 | 2023-03-05 | Apparel | 160.00 | web |
| 4 | 2024-01-12 | Apparel | 150.00 | web |
| 5 | 2024-02-08 | Apparel | 210.00 | app |
| 6 | 2024-03-18 | Apparel | 240.00 | web |
| 7 | 2023-01-20 | Home | 90.00 | app |
| 8 | 2023-02-14 | Home | 110.00 | web |
| 9 | 2024-01-09 | Home | 130.00 | app |
| 10 | 2024-02-21 | Home | 170.00 | web |
Expected output
| forecast_month | category | baseline_3mo_avg | yoy_seasonality_factor | forecast_amount |
|---|
| 2024-04-01 | Apparel | 216.67 | 1.20 | 260.00 |
| 2024-04-01 | Home | 150.00 | 1.15 | 172.50 |
