Your question is Monthly Studio Delivery Performance. 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 delivery event data for Lucid Studios and asked to analyze monthly delivery performance by studio. Write a PostgreSQL query that returns one row per studio per delivery month, showing the average dwell time in hours and the on-time delivery rate. Treat dwell time as the time between vehicle arrival at the studio and customer handoff. A delivery is on time when the actual handoff date is on or before the scheduled delivery date. Only include deliveries that were completed and have a non-null actual handoff timestamp.
| Column | Type | Description |
|---|---|---|
| studio_idPK | INT | Primary key for each Lucid Studio |
| studio_name | VARCHAR(100) | Studio name |
| region | VARCHAR(50) | Studio region |
| Column | Type | Description |
|---|---|---|
| delivery_idPK | INT | Primary key for each delivery |
| studio_id | INT | Studio handling the delivery |
| order_id | INT | Customer order identifier |
| scheduled_delivery_date | DATE | Planned customer delivery date |
| actual_handoff_ts | TIMESTAMP | Actual customer handoff timestamp |
| delivery_status | VARCHAR(30) | Delivery status |
| Column | Type | Description |
|---|---|---|
| event_idPK | INT | Primary key for each event |
| delivery_id | INT | Related delivery |
| event_type | VARCHAR(40) | Event type such as vehicle_arrived |
| event_ts | TIMESTAMP | Event timestamp |