Find Fulfillment Bottlenecks from Timestamps

HardSQL · PostgreSQL

Problem

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.

Schema

order_fulfillment_events

Column	Type	Description
order_id	BIGINT	Unique order identifier
event_name	VARCHAR(50)	Fulfillment stage name
event_ts	TIMESTAMP	Timestamp when the stage occurred
store_id	INT	Store handling the order

Tablesorder_fulfillment_events

Your question is Find Fulfillment Bottlenecks from Timestamps. Start with the requirements and the one table in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.