SQL for Week-Over-Week Shrink | Dataford Interview Questions

Nordstrom operations managers review inventory shrinkage by region and department. Write a PostgreSQL query that compares each department's shrinkage rate with the immediately preceding week.

Shrinkage units are defined as expected_units - counted_units. The shrinkage rate is shrinkage units divided by expected units, expressed as a percentage.

Requirements

Aggregate inventory counts by department and Monday-based calendar week.
Return only departments with a non-null region and a prior week available for comparison.
Show the current shrinkage rate, prior week's shrinkage rate, and the week-over-week change in percentage points.
Order results by region, department name, and week start.

Schema

departments

Column	Type	Description
department_idPK	INTEGER	Unique department identifier
region	VARCHAR(50)	Nordstrom operating region
department_name	VARCHAR(100)	Department name

inventory_counts

Column	Type	Description
count_idPK	INTEGER	Unique inventory count identifier
department_id	INTEGER	Department associated with the count
count_date	DATE	Date of the physical inventory count
expected_units	INTEGER	Units expected according to inventory records
counted_units	INTEGER	Units physically counted
audit_note	VARCHAR(200)	Optional inventory audit note

Your question is SQL for Week-Over-Week Shrink. 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