Compute Delta Between Events | Dataford Interview Questions

The monday Work Management team wants to monitor how long items remain open on a board. Given separate creation and completion event tables, write a PostgreSQL query that calculates the elapsed time between the two timestamped events for every created item.

Requirements

Start with every item in item_created_events, including items without a completion event.
Join a matching completion event from item_completed_events using item_id.
Return the elapsed time in hours as delta_hours, rounded to two decimal places. Return NULL when the completion timestamp is unavailable.
Order the results chronologically by the creation timestamp.

Schema

item_created_events

Column	Type	Description
item_idPK	BIGINT	Unique monday item identifier
created_at	TIMESTAMPTZ	Timestamp when the item was created

item_completed_events

Column	Type	Description
item_idPK	BIGINT	monday item identifier associated with the completion event
completed_at	TIMESTAMPTZ	Timestamp when the item was completed

Your question is Compute Delta Between Events. Start with the requirements and the two 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