Write a SQL query to analyze store performance by day, joining sales, staffing, and inventory tables to identify locations with low sales, understaffing, and stockouts. Treat low sales as daily sales below 500, understaffing as staffed hours below 80% of scheduled hours, and a stockout as any SKU with zero units on hand.
store_code, business_date, daily_sales, scheduled_hours, staffed_hours, and stockout_sku_count.business_date, then store_code.| Column | Type | Description |
|---|---|---|
| store_idPK | INT | Unique Dunkin store identifier |
| store_code | VARCHAR(20) | Operational store code |
| store_name | VARCHAR(100) | Store display name |
| Column | Type | Description |
|---|---|---|
| sale_idPK | INT | Unique sales transaction identifier |
| store_id | INT | Referenced Dunkin store |
| sale_date | DATE | Business date of the sale |
| net_sales | DECIMAL(10,2) | Net transaction sales amount |
| Column | Type | Description |
|---|---|---|
| staffing_idPK | INT | Unique staffing record identifier |
| store_id | INT | Referenced Dunkin store |
| work_date | DATE | Staffing business date |
| scheduled_hours | DECIMAL(6,2) | Scheduled labor hours |
| staffed_hours | DECIMAL(6,2) | Actual staffed labor hours |
| Column | Type | Description |
|---|---|---|
| inventory_idPK | INT | Unique inventory snapshot identifier |
| store_id | INT | Referenced Dunkin store |
| inventory_date | DATE | Inventory snapshot date |
| sku | VARCHAR(40) | Inventory item identifier |
| units_on_hand | INT | Units available at the snapshot |