SQL Shrink Rate by Month | Dataford Interview Questions

Write a SQL query to find stores with the highest shrink rate by month and compare them to the chain average at Americaneagle.

Use active stores with valid monthly inventory counts. Define the chain average as the weighted shrink rate across included stores, using expected units as the weight.

Output

One row per highest-shrink store and month, including ties.
Columns: month, store_name, shrink_rate_pct, chain_average_pct, and difference_from_chain_pct.
Sort by month ascending, shrink rate descending, then store name ascending.

Schema

stores

Column	Type	Description
store_idPK	INT	Unique store identifier
store_name	VARCHAR(100)	Americaneagle store name
region	VARCHAR(50)	Operating region
is_active	BOOLEAN	Whether the store is currently active

monthly_inventory

Column	Type	Description
inventory_idPK	INT	Unique monthly inventory record
store_id	INT	Referenced store identifier
month_start	DATE	First day of the inventory month
expected_units	INT	Units expected according to inventory records
counted_units	INT	Units found during the physical count

Your question is SQL Shrink Rate by Month. 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