Write SQL and Python, run instantly in your browser, and track your progress.
You are a Product Analytics Manager at Amazon. The Catalog Operations team is conducting a comprehensive inventory audit to understand which products have historical order data, regardless of their current active status. They need to segment these products by their active status to identify products that are still generating revenue versus those that were discontinued but have order history.
Your task is to create a report that lists all products that have been ordered at least once, clearly labeled by their current active status. Use CTEs to separate active and inactive products, then combine them with UNION ALL to create a unified view.
| Column Name | Type |
|---|---|
| product_id | INTEGER |
| sku | TEXT |
| product_name |
You are a Product Analytics Manager at Amazon. The Catalog Operations team is conducting a comprehensive inventory audit to understand which products have historical order data, regardless of their current active status. They need to segment these products by their active status to identify products that are still generating revenue versus those that were discontinued but have order history.
Your task is to create a report that lists all products that have been ordered at least once, clearly labeled by their current active status. Use CTEs to separate active and inactive products, then combine them with UNION ALL to create a unified view.
| Column Name | Type |
|---|---|
| product_id | INTEGER |
| sku | TEXT |
| product_name |
| TEXT |
| TEXT |
| category | TEXT |
| category | TEXT |
| price | REAL |
| price | REAL |
| currency | TEXT |
| currency | TEXT |
| is_active | INTEGER |
| is_active | INTEGER |
| weight_kg | REAL |
| weight_kg | REAL |
| created_at | TEXT |
| created_at | TEXT |
| Column Name | Type |
|---|---|
| order_item_id | INTEGER |
| order_id | INTEGER |
| product_id | INTEGER |
| quantity | INTEGER |
| unit_price | REAL |
| line_subtotal | REAL |
| Column Name | Type |
|---|---|
| order_item_id | INTEGER |
| order_id | INTEGER |
| product_id | INTEGER |
| quantity | INTEGER |
| unit_price | REAL |
| line_subtotal | REAL |
| product_id | sku | product_name | category | price | currency | is_active | weight_kg | created_at |
|---|---|---|---|---|---|---|---|---|
| 1 | SKU-10001 | Casper Original Mattress Queen | Home | 1095.0 | USD |
| product_id | sku | product_name | category | price | currency | is_active | weight_kg | created_at |
|---|---|---|---|---|---|---|---|---|
| 1 | SKU-10001 | Casper Original Mattress Queen | Home | 1095.0 | USD |
| order_item_id | order_id | product_id | quantity | unit_price | line_subtotal | tax_amount | discount_amount | fulfillment_status | return_requested_at | return_reason |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 10 | 1 |
| order_item_id | order_id | product_id | quantity | unit_price | line_subtotal | tax_amount | discount_amount | fulfillment_status | return_requested_at | return_reason |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 10 | 1 |
| product_id | product_name | product_status |
|---|---|---|
| 1 | Casper Original Mattress Queen | active |
| 2 | Dyson Supersonic Hair Dryer | active |
| 3 | CalDigit TS4 Thunderbolt Dock | active |
| 4 | Under Armour Tech 2.0 T-Shirt | active |
| 5 | Staples TRU RED Copy Paper 10-Re | active |
| product_id | product_name | product_status |
|---|---|---|
| 1 | Casper Original Mattress Queen | active |
| 2 | Dyson Supersonic Hair Dryer | active |
| 3 | CalDigit TS4 Thunderbolt Dock | active |
| 4 | Under Armour Tech 2.0 T-Shirt | active |
| 5 | Staples TRU RED Copy Paper 10-Re | active |
Showing first 5 of 49 rows. All active ordered products listed first, followed by inactive ordered products.
Showing first 5 of 49 rows. All active ordered products listed first, followed by inactive ordered products.
1. Output Columns:
2. Data Selection:
3. CTEs and Set Operations:
4. Ordering:
1. Output Columns:
2. Data Selection:
3. CTEs and Set Operations:
4. Ordering:
| tax_amount | REAL |
| tax_amount | REAL |
| discount_amount | REAL |
| discount_amount | REAL |
| fulfillment_status | TEXT |
| fulfillment_status | TEXT |
| return_requested_at | TEXT |
| return_requested_at | TEXT |
| return_reason | TEXT |
| return_reason | TEXT |
| 1 |
| 1 |
| 31.75 |
| 31.75 |
| 2025-02-14 10:57:38 |
| 2025-02-14 10:57:38 |
| 2 | SKU-10002 | Dyson Supersonic Hair Dryer | Beauty | 429.99 | USD | 1 | 0.66 | 2025-02-27 07:20:29 |
| 2 | SKU-10002 | Dyson Supersonic Hair Dryer | Beauty | 429.99 | USD | 1 | 0.66 | 2025-02-27 07:20:29 |
| 3 | SKU-10003 | CalDigit TS4 Thunderbolt Dock | Office | 399.99 | USD | 1 | 0.5 | 2024-12-11 00:49:57 |
| 3 | SKU-10003 | CalDigit TS4 Thunderbolt Dock | Office | 399.99 | USD | 1 | 0.5 | 2024-12-11 00:49:57 |
| 4 | SKU-10004 | Under Armour Tech 2.0 T-Shirt | Sports | 25.0 | USD | 1 | 0.15 | 2024-11-22 04:49:00 |
| 4 | SKU-10004 | Under Armour Tech 2.0 T-Shirt | Sports | 25.0 | USD | 1 | 0.15 | 2024-11-22 04:49:00 |
| 5 | SKU-10005 | Staples TRU RED Copy Paper 10-Ream | Office | 54.99 | USD | 1 | 22.68 | 2025-01-10 01:43:59 |
| 5 | SKU-10005 | Staples TRU RED Copy Paper 10-Ream | Office | 54.99 | USD | 1 | 22.68 | 2025-01-10 01:43:59 |
| 50.96 |
| 50.96 |
| 50.96 |
| 50.96 |
| 4.14 |
| 4.14 |
| 0.0 |
| 0.0 |
| shipped |
| shipped |
| 2 | 2 | 8 | 3 | 200.51 | 601.53 | 53.2 | 0.0 | delivered |
| 2 | 2 | 8 | 3 | 200.51 | 601.53 | 53.2 | 0.0 | delivered |
| 3 | 3 | 44 | 2 | 34.13 | 68.26 | 6.25 | 0.99 | shipped |
| 3 | 3 | 44 | 2 | 34.13 | 68.26 | 6.25 | 0.99 | shipped |
| 4 | 4 | 47 | 1 | 290.99 | 290.99 | 23.29 | 0.0 | shipped |
| 4 | 4 | 47 | 1 | 290.99 | 290.99 | 23.29 | 0.0 | shipped |
| 5 | 5 | 36 | 3 | 189.87 | 569.61 | 46.78 | 79.38 | backordered |
| 5 | 5 | 36 | 3 | 189.87 | 569.61 | 46.78 | 79.38 | backordered |