Your question is Clean Vehicle Delivery Timeline Records. 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 raw vehicle delivery timeline data with duplicate status events and missing milestone dates. Write a PostgreSQL query that returns one row per delivery for Lucid Air and Lucid Gravity vehicles, keeping only the latest record for each (delivery_id, event_type) pair, then summarizing the cleaned timeline. Your output should include the vehicle model, delivery center, order date, confirmed delivery date, the number of distinct event types captured, and a delivery status flag that marks a record as incomplete_timeline when either the order date or confirmed delivery date is missing.
Use the delivery center table so the result includes center names even if some deliveries have no matching cleaned event rows.
| Column | Type | Description |
|---|---|---|
| delivery_idPK | INT | Unique delivery record |
| vehicle_vin | VARCHAR(20) | Vehicle VIN |
| vehicle_model | VARCHAR(30) | Vehicle model |
| delivery_center_id | INT | Reference to delivery center |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Unique raw event row |
| delivery_id | INT | Reference to delivery |
| event_type | VARCHAR(30) | Timeline milestone type |
| event_timestamp | TIMESTAMP | Timestamp recorded for the milestone |
| Column | Type | Description |
|---|---|---|
| delivery_center_idPK | INT | Delivery center ID |
| center_name | VARCHAR(50) | Delivery center name |