Task
BrightCart needs a client-facing order report, but the source data contains duplicate order records and some rows have missing product_id values. Write a PostgreSQL query to produce a cleaned report for January 2024.
Requirements
- Deduplicate
orders_raw by keeping only the latest row per order_id based on updated_at.
- Join the cleaned orders to
products using product_id.
- If
product_id is missing or does not match a product, label the product as 'Unknown Product' and the category as 'Unknown'.
- Return one row per cleaned order with:
order_id, client_name, order_date, product_name, category, quantity, and line_amount (quantity * unit_price).
- Include only orders where
order_date is between 2024-01-01 and 2024-01-31, ordered by order_date, then order_id.
Table Definitions
clients
| column | type | description |
|---|
| client_id | INT | Client identifier |
| client_name | VARCHAR(100) | Client name |
products
| column | type | description |
|---|
| product_id | INT | Product identifier |
| product_name | VARCHAR(100) | Product name |
| category | VARCHAR(50) | Product category |
| unit_price | DECIMAL(10,2) | Price per unit |
orders_raw
| column | type | description |
|---|
| row_id | INT | Raw row identifier |
| order_id | INT | Business order identifier |
| client_id | INT | Client placing the order |
| product_id | INT | Ordered product; may be NULL |
| order_date | DATE | Order date |
| quantity | INT | Units ordered |
| updated_at | TIMESTAMP | Last update time for the raw row |
Sample Data
clients
| client_id | client_name |
|---|
| 1 | Acme Retail |
| 2 | Northwind Stores |
| 3 | BlueSky Market |
products
| product_id | product_name | category | unit_price |
|---|
| 101 | Wireless Mouse | Electronics | 25.00 |
| 102 | USB-C Hub | Electronics | 45.00 |
| 103 | Notebook Set | Office | 12.00 |
orders_raw
| row_id | order_id | client_id | product_id | order_date | quantity | updated_at |
|---|
| 1 | 5001 | 1 | 101 | 2024-01-05 | 2 | 2024-01-05 09:00:00 |
| 2 | 5001 | 1 | 101 | 2024-01-05 | 3 | 2024-01-05 11:00:00 |
| 3 | 5002 | 2 | NULL | 2024-01-07 | 1 | 2024-01-07 10:00:00 |
| 4 | 5003 | 3 | 103 | 2024-01-10 | 4 | 2024-01-10 08:30:00 |
| 5 | 5003 | 3 | 103 | 2024-01-10 | 5 | 2024-01-10 12:00:00 |
Expected Output
| order_id | client_name | order_date | product_name | category | quantity | line_amount |
|---|
| 5001 | Acme Retail | 2024-01-05 | Wireless Mouse | Electronics | 3 | 75.00 |
| 5002 | Northwind Stores | 2024-01-07 | Unknown Product | Unknown | 1 | 0.00 |
| 5003 | BlueSky Market | 2024-01-10 | Notebook Set | Office | 5 | 60.00 |
