Your question is Deduplicate Shipment Events for Lead Time. 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.
You are given shipment event data from a defense supply chain workflow and need to produce accurate lead-time reporting for Lattice shipments. Some rows are duplicate event records for the same shipment and status, and some timestamps are missing. Write a PostgreSQL query that returns one row per shipment with a cleaned lead time in days from created_at to delivered time, using the earliest valid CREATED event and the latest valid DELIVERED event after deduplication. Exclude shipments where either side of the lead-time calculation is still null after cleaning.
| Column | Type | Description |
|---|---|---|
| shipment_idPK | INT | Unique shipment identifier |
| supplier_id | INT | Supplier associated with the shipment |
| route_code | VARCHAR(20) | Shipment route or lane code |
| created_at | TIMESTAMP | Base shipment creation timestamp |
| expected_delivery_date | DATE | Planned delivery date |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique event identifier |
| shipment_id | INT | Shipment tied to the event |
| event_type | VARCHAR(20) | Event status such as CREATED, IN_TRANSIT, or DELIVERED |
| event_time | TIMESTAMP | Timestamp of the event |
| source_system | VARCHAR(20) | System that emitted the event |
| Column | Type | Description |
|---|---|---|
| supplier_idPK | INT | Unique supplier identifier |
| supplier_name | VARCHAR(100) | Supplier name |
| supplier_tier | VARCHAR(20) | Supplier tier classification |