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 |