Max Goods in Warehouse | Dataford Interview Questions

Flexport's warehouse operations team wants to identify peak inventory levels across its facilities. Inventory changes are recorded as completed or non-completed movement events.

Write a PostgreSQL query to determine the maximum number of goods recorded at each warehouse and the interval during which that maximum level was maintained.

Requirements

Consider only events where status = 'completed'.
Calculate each warehouse's inventory level as a cumulative sum of quantity_change, ordered by event_time and event_id.
Return every interval where the warehouse was at its maximum recorded inventory level. The interval ends at the next completed event for that warehouse. A NULL end indicates that no later completed event exists.
Return results ordered by warehouse_id and interval start time.

Schema

warehouses

Column	Type	Description
warehouse_idPK	INT	Unique warehouse identifier
warehouse_name	VARCHAR(100)	Flexport warehouse name
region	VARCHAR(50)	Geographic operating region

inventory_events

Column	Type	Description
event_idPK	INT	Unique inventory event identifier
warehouse_id	INT	Referenced warehouse
event_time	TIMESTAMP	Time when the movement was recorded
quantity_change	INT	Number of goods added to or removed from inventory
status	VARCHAR(20)	Movement processing status

Your question is Max Goods in Warehouse. 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