Task
NovaCart launched a new product on a known launch date and wants to compare product performance immediately before and after launch. Write a SQL query to measure pre-launch vs post-launch performance for each launched product.
Requirements
- For each launched product, compare the 30 days before launch with the 30 days after launch.
- Return these columns:
product_id, product_name, launch_date, pre_orders, post_orders, pre_revenue, post_revenue, revenue_change_pct.
- Only include products where
launch_date is not null.
- Sort by
revenue_change_pct descending, then product_id ascending.
Table Definitions
products
| column | type | description |
|---|
| product_id | INT | Product identifier |
| product_name | VARCHAR(100) | Product name |
| category | VARCHAR(50) | Product category |
| launch_date | DATE | Product launch date |
orders
| column | type | description |
|---|
| order_id | INT | Order identifier |
| customer_id | INT | Customer placing the order |
| order_date | DATE | Order date |
| status | VARCHAR(20) | Order status |
order_items
| column | type | description |
|---|
| order_item_id | INT | Order line identifier |
| order_id | INT | Related order |
| product_id | INT | Purchased product |
| quantity | INT | Units purchased |
| unit_price | DECIMAL(10,2) | Price per unit |
Sample Data
products
| product_id | product_name | category | launch_date |
|---|
| 103 | Smart Bottle | Fitness | 2024-03-01 |
| 101 | Alpha Earbuds | Audio | 2024-02-15 |
| 108 | Solar Charger | Accessories | 2024-03-10 |
orders
| order_id | customer_id | order_date | status |
|---|
| 1004 | 4 | 2024-02-16 | completed |
| 1001 | 1 | 2024-01-20 | completed |
| 1010 | 10 | 2024-03-15 | completed |
order_items
| order_item_id | order_id | product_id | quantity | unit_price |
|---|
| 5001 | 1001 | 101 | 1 | 80.00 |
| 5006 | 1004 | 101 | 3 | 95.00 |
| 5014 | 1010 | 108 | 2 | 60.00 |
Expected Output
| product_id | product_name | launch_date | pre_orders | post_orders | pre_revenue | post_revenue | revenue_change_pct |
|---|
| 108 | Solar Charger | 2024-03-10 | 1 | 2 | 55.00 | 180.00 | 227.27 |
| 101 | Alpha Earbuds | 2024-02-15 | 2 | 2 | 170.00 | 380.00 | 123.53 |
| 103 | Smart Bottle | 2024-03-01 | 2 | 2 | 110.00 | 165.00 | 50.00 |
