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 |