Welcome to the SQL screen.
The question is on your right: Deduplicate Shipment Events for Lead Time. Read through the requirements and the three tables first.
Run and submit your code as often as you need. You also have five interviewer messages this session - want to talk through your approach, or are you ready to start coding?
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 |