Task
You are given monthly performance data for delivery regions. Write a PostgreSQL query that compares each region’s current month performance to its previous month using a self-join. Return only months where a prior month exists, along with the month-over-month change in orders and revenue.
Schema
| column | type | description |
|---|
| region_id | INT | Region identifier |
| region_name | VARCHAR(100) | Region name |
| month_start | DATE | First day of the month |
| orders_completed | INT | Completed orders in the month |
| gross_revenue | NUMERIC(12,2) | Gross revenue for the month |
Sample data
| region_id | region_name | month_start | orders_completed | gross_revenue |
|---|
| 1 | North Bay | 2024-01-01 | 1200 | 54000.00 |
| 1 | North Bay | 2024-02-01 | 1320 | 59400.00 |
| 1 | North Bay | 2024-04-01 | 1280 | 57600.00 |
| 2 | South Loop | 2024-01-01 | 900 | 40500.00 |
| 2 | South Loop | 2024-02-01 | 870 | 39150.00 |
| 2 | South Loop | 2024-03-01 | 930 | 41850.00 |
| 3 | East Point | 2024-02-01 | 640 | 28800.00 |
| 3 | East Point | 2024-03-01 | 700 | 31500.00 |
| 4 | West Ridge | 2024-01-01 | 0 | 0.00 |
| 4 | West Ridge | 2024-02-01 | 50 | 2250.00 |
Expected output
| region_name | month_start | orders_completed | prev_month_orders | orders_change | gross_revenue | prev_month_revenue | revenue_change |
|---|
| North Bay | 2024-02-01 | 1320 | 1200 | 120 | 59400.00 | 54000.00 | 5400.00 |
| South Loop | 2024-02-01 | 870 | 900 | -30 | 39150.00 | 40500.00 | -1350.00 |
| South Loop | 2024-03-01 | 930 | 870 | 60 | 41850.00 | 39150.00 | 2700.00 |
| East Point | 2024-03-01 | 700 | 640 | 60 | 31500.00 | 28800.00 | 2700.00 |
| West Ridge | 2024-02-01 | 50 | 0 | 50 | 2250.00 | 0.00 | 2250.00 |
