Your question is Clean Duplicate Orders for Reporting. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
orders_raw by keeping only the latest row per order_id based on updated_at.products using product_id.product_id is missing or does not match a product, label the product as 'Unknown Product' and the category as 'Unknown'.order_id, client_name, order_date, product_name, category, quantity, and line_amount (quantity * unit_price).order_date is between 2024-01-01 and 2024-01-31, ordered by order_date, then order_id.| Column | Type | Description |
|---|---|---|
| client_idPK | INT | Primary key for the client |
| client_name | VARCHAR(100) | Client company name |
| Column | Type | Description |
|---|---|---|
| product_idPK | INT | Primary key for the product |
| product_name | VARCHAR(100) | Display name of the product |
| category | VARCHAR(50) | Product category |
| unit_price | DECIMAL(10,2) | Unit price used to calculate line amount |
| Column | Type | Description |
|---|---|---|
| row_idPK | INT | Primary key for the raw order row |
| order_id | INT | Business order identifier that may appear multiple times |
| client_id | INT | References the client placing the order |
| product_id | INT | References the ordered product; may be NULL or invalid |
| order_date | DATE | Date the order was placed |
| quantity | INT | Number of units ordered |
| updated_at | TIMESTAMP | Timestamp of the latest raw row update |