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 |