SQL for Inventory Shrinkage | Dataford Interview Questions

Write a SQL query to identify Acme Fresh Market stores with the highest inventory shrinkage by month, joining inventory counts, sales, and store metadata tables. Calculate shrinkage as opening inventory plus received units minus sold units minus counted units. Include tied stores at the highest shrinkage for each month.

Output

One row per highest-shrinkage store and month
Columns: month_start, store_id, store_name, shrinkage_units, shrinkage_rate, and month_rank
Sort by month_start, then month_rank, then store_id

Schema

stores

Column	Type	Description
store_idPK	INT	Unique Acme Fresh Market store identifier
store_name	VARCHAR(100)	Store display name
city	VARCHAR(100)	City where the store operates

inventory_counts

Column	Type	Description
count_idPK	INT	Unique inventory count identifier
store_id	INT	Store associated with the count
count_date	DATE	Date of the physical inventory count
opening_units	INT	Units available at the start of the period
received_units	INT	Units received during the period
counted_units	INT	Units physically counted at period end

sales

Column	Type	Description
sale_idPK	INT	Unique sales transaction identifier
store_id	INT	Store where the sale occurred
sale_date	DATE	Date of the sale
units_sold	INT	Units sold in the transaction

Your question is SQL for Inventory Shrinkage. 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