Amazon's order-quality team needs to identify orders that may have been created more than once. Treat orders as duplicates when they share the same customer_id, order_date, total_amount, and shipping_address.
Write a PostgreSQL query that returns each duplicate order group and the IDs of all orders in that group.
NULL shipping addresses as equal for duplicate detection.customer_id, customer_email, order_date, total_amount, shipping_address, duplicate_count, duplicate_order_ids.customer_id ascending by order_date ascending, then total_amount ascending with NULL values last.duplicate_order_ids in ascending order_id order, separated by ', '.NULL total_amount values as equal when identifying and retrieving duplicate groups.NULL for customer_email when no matching customer exists.| Column | Type | Description |
|---|---|---|
| order_idPK | INT | Unique order identifier |
| customer_id | INT | References customers.customer_id |
| order_date | DATE | Date the order was placed |
| total_amount | NUMERIC(10,2) | Order total |
| shipping_address | VARCHAR(200) | Delivery address |
| Column | Type | Description |
|---|---|---|
| customer_idPK | INT | Unique customer identifier |
| VARCHAR(255) | Customer email address |