Your question is Analyze Inventory Shrinkage Drivers. Start with the requirements and the three 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.
People First Service Center needs a weekly view of inventory shrinkage to help Operations Managers prioritize loss prevention. Write a PostgreSQL query that summarizes shrinkage by week, store, product category, and store manager, then identifies the two largest loss drivers for each store and week.
LEFT JOIN operations so audits with an unknown store or an unassigned manager remain visible, using clear fallback labels.Use audits from January 2025 only. Exclude groups with no positive monetary loss.
| Column | Type | Description |
|---|---|---|
| audit_idPK | INT | Inventory audit identifier |
| audit_date | DATE | Date of the inventory audit |
| store_id | INT | Audited store identifier |
| category | VARCHAR(50) | Product category |
| expected_units | INT | Units expected from inventory records |
| counted_units | INT | Units counted during the audit |
| unit_cost | NUMERIC(10,2) | Cost per unit |
| Column | Type | Description |
|---|---|---|
| store_idPK | INT | Store identifier |
| store_name | VARCHAR(100) | People First Service Center store name |
| manager_id | INT | Assigned manager identifier |
| Column | Type | Description |
|---|---|---|
| manager_idPK | INT | Manager identifier |
| manager_name | VARCHAR(100) | Manager name |