Task
At Northstar Retail, analysts want to compare each store's daily revenue to its previous recorded day. Write a PostgreSQL query that uses LAG or LEAD to compare a day's revenue with the prior row for the same store.
Requirements
- Aggregate order revenue by
store_id and order_date.
- For each store, compare each day's revenue to the previous day present in the data using a window function.
- Return
store_id, store_name, order_date, daily_revenue, previous_day_revenue, revenue_change, and a change_type column with values Increase, Decrease, or No Prior Day.
- Exclude cancelled orders from the revenue calculation.
- Order the final output by
store_id and order_date.
Table Definitions
stores
| column | type | description |
|---|
| store_id | INT | Unique store identifier |
| store_name | VARCHAR(100) | Store name |
| region | VARCHAR(50) | Store region |
orders
| column | type | description |
|---|
| order_id | INT | Unique order identifier |
| store_id | INT | Store that received the order |
| order_date | DATE | Order date |
| amount | NUMERIC(10,2) | Order amount |
| status | VARCHAR(20) | Order status |
Sample Data
stores
| store_id | store_name | region |
|---|
| 2 | Brooklyn | East |
| 1 | Manhattan | East |
| 4 | Austin | South |
| 3 | Seattle | West |
| 5 | Phoenix | NULL |
orders
| order_id | store_id | order_date | amount | status |
|---|
| 108 | 2 | 2024-04-03 | 60.00 | completed |
| 101 | 1 | 2024-04-01 | 120.00 | completed |
| 111 | 3 | 2024-04-01 | 300.00 | completed |
| 104 | 1 | 2024-04-02 | 50.00 | completed |
| 106 | 2 | 2024-04-01 | 80.00 | completed |
| 113 | 3 | 2024-04-03 | 0.00 | completed |
| 109 | 2 | 2024-04-03 | 40.00 | completed |
| 103 | 1 | 2024-04-02 | 200.00 | cancelled |
| 110 | 2 | 2024-04-04 | NULL | completed |
| 112 | 3 | 2024-04-02 | -20.00 | completed |
Expected Output
| store_id | store_name | order_date | daily_revenue | previous_day_revenue | revenue_change | change_type |
|---|
| 1 | Manhattan | 2024-04-01 | 120.00 | NULL | NULL | No Prior Day |
| 1 | Manhattan | 2024-04-02 | 200.00 | 120.00 | 80.00 | Increase |
| 1 | Manhattan | 2024-04-04 | 150.00 | 200.00 | -50.00 | Decrease |
| 2 | Brooklyn | 2024-04-01 | 80.00 | NULL | NULL | No Prior Day |
| 2 | Brooklyn | 2024-04-03 | 100.00 | 80.00 | 20.00 | Increase |
| 2 | Brooklyn | 2024-04-04 | NULL | 100.00 | NULL | Decrease |
| 3 | Seattle | 2024-04-01 | 300.00 | NULL | NULL | No Prior Day |
| 3 | Seattle | 2024-04-02 | -20.00 | 300.00 | -320.00 | Decrease |
| 3 | Seattle | 2024-04-03 | 0.00 | -20.00 | 20.00 | Increase |
