Time Gap Between Consecutive Deliveries | Dataford Interview Questions

Given tables for orders, merchants, and Dashers, write a query using LAG or LEAD to identify the time gap between consecutive deliveries for each driver.

Use completed deliveries with non-null delivery timestamps. Return only deliveries that have a previous delivery for the same Dasher.

Output

One row per delivery with a previous delivery by that Dasher.
Include dasher_id, dasher_name, order_id, merchant_name, delivery_completed_at, previous_delivery_at, and gap_minutes.
Order by dasher_id, delivery_completed_at, and order_id.

Schema

orders

Column	Type	Description
order_idPK	INT	Unique delivery order identifier
merchant_id	INT	Merchant associated with the order
dasher_id	INT	Dasher assigned to the order
status	VARCHAR(20)	Current order status
delivery_completed_at	TIMESTAMP	Timestamp when the delivery was completed

merchants

Column	Type	Description
merchant_idPK	INT	Unique merchant identifier
merchant_name	VARCHAR(100)	Merchant display name
city	VARCHAR(60)	Merchant city

dashers

Column	Type	Description
dasher_idPK	INT	Unique Dasher identifier
dasher_name	VARCHAR(100)	Dasher name
vehicle_type	VARCHAR(30)	Dasher vehicle type

Your question is Time Gap Between Consecutive Deliveries. Start with the requirements and the three tables 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.

Problem