SQL for Inconsistent Inventory | Dataford Interview Questions

Your question is SQL for Inconsistent Inventory. Start with the requirements and the two tables on the right.

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

Problem

LEGO Group inventory feeds can contain multiple snapshots for the same set, warehouse, and date. Write a PostgreSQL query to identify conflicting records that may indicate a reporting or ingestion problem.

Requirements

Find set and date combinations represented in at least two warehouses.
Within those combinations, identify warehouses with multiple records whose on-hand or reserved quantities differ.
Return the warehouse, region, LEGO set, snapshot date, record count, number of distinct quantity versions, and minimum and maximum quantities.
Sort the results by snapshot date, set ID, and warehouse ID.

Schema

warehouses

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

inventory_snapshots

Column	Type	Description
snapshot_idPK	INT	Unique inventory snapshot record
warehouse_id	INT	Warehouse that reported the snapshot
set_id	INT	LEGO set identifier
snapshot_date	DATE	Date represented by the inventory snapshot
quantity_on_hand	INT	Physical units recorded at the warehouse
quantity_reserved	INT	Units reserved for orders