You are given fulfillment event timestamps for each order. Write a PostgreSQL query that identifies where the bottleneck is occurring in the fulfillment chain by calculating the elapsed time between consecutive stages for each order and reporting the stage with the largest average delay.
| table | column | type | description |
|---|---|---|---|
order_fulfillment_events | order_id | BIGINT | Unique order identifier |
order_fulfillment_events | event_name | VARCHAR(50) | Fulfillment stage name, such as placed, accepted, picked, packed, dispatched, or delivered |
order_fulfillment_events | event_ts | TIMESTAMP | Timestamp when the stage occurred |
order_fulfillment_events | store_id | INT | Store handling the order |
| order_id | event_name | event_ts | store_id |
|---|---|---|---|
| 1001 | placed | 2024-06-01 08:00:00 | 10 |
| 1001 | accepted | 2024-06-01 08:04:00 | 10 |
| 1001 | picked | 2024-06-01 08:18:00 | 10 |
| 1001 | packed | 2024-06-01 08:25:00 | 10 |
| 1001 | dispatched | 2024-06-01 08:40:00 | 10 |
| 1001 | delivered | 2024-06-01 09:05:00 | 10 |
| 1002 | placed | 2024-06-01 09:10:00 | 10 |
| 1002 | accepted | 2024-06-01 09:12:00 | 10 |
| 1002 | picked | 2024-06-01 09:35:00 | 10 |
| 1002 | packed | 2024-06-01 09:41:00 | 10 |
| bottleneck_stage | avg_delay_minutes |
|---|---|
| picked | 17.5 |
You are given fulfillment event timestamps for each order. Write a PostgreSQL query that identifies where the bottleneck is occurring in the fulfillment chain by calculating the elapsed time between consecutive stages for each order and reporting the stage with the largest average delay.
| table | column | type | description |
|---|---|---|---|
order_fulfillment_events | order_id | BIGINT | Unique order identifier |
order_fulfillment_events | event_name | VARCHAR(50) | Fulfillment stage name, such as placed, accepted, picked, packed, dispatched, or delivered |
order_fulfillment_events | event_ts | TIMESTAMP | Timestamp when the stage occurred |
order_fulfillment_events | store_id | INT | Store handling the order |
| order_id | event_name | event_ts | store_id |
|---|---|---|---|
| 1001 | placed | 2024-06-01 08:00:00 | 10 |
| 1001 | accepted | 2024-06-01 08:04:00 | 10 |
| 1001 | picked | 2024-06-01 08:18:00 | 10 |
| 1001 | packed | 2024-06-01 08:25:00 | 10 |
| 1001 | dispatched | 2024-06-01 08:40:00 | 10 |
| 1001 | delivered | 2024-06-01 09:05:00 | 10 |
| 1002 | placed | 2024-06-01 09:10:00 | 10 |
| 1002 | accepted | 2024-06-01 09:12:00 | 10 |
| 1002 | picked | 2024-06-01 09:35:00 | 10 |
| 1002 | packed | 2024-06-01 09:41:00 | 10 |
| bottleneck_stage | avg_delay_minutes |
|---|---|
| picked | 17.5 |
