Write a SQL query to analyze store performance by week, joining sales, payroll, and inventory tables to calculate sell-through and labor efficiency.
Return weekly results for stores with sales activity. Sell-through is units sold divided by units sold plus ending inventory, expressed as a percentage. Labor efficiency is net sales divided by labor hours.
store_id, week_start, net_sales, units_sold, labor_hours, labor_efficiency, ending_inventory, and sell_through_pct.store_id, then week_start.| Column | Type | Description |
|---|---|---|
| sale_idPK | INT | Unique sales transaction identifier |
| store_id | VARCHAR(10) | American Eagle store identifier |
| sale_date | DATE | Transaction date |
| units_sold | INT | Units sold in the transaction |
| net_sales | NUMERIC(12,2) | Net sales amount after discounts |
| Column | Type | Description |
|---|---|---|
| payroll_idPK | INT | Unique payroll record identifier |
| store_id | VARCHAR(10) | American Eagle store identifier |
| shift_date | DATE | Date worked |
| labor_hours | NUMERIC(8,2) | Paid labor hours |
| Column | Type | Description |
|---|---|---|
| inventory_idPK | INT | Unique inventory snapshot identifier |
| store_id | VARCHAR(10) | American Eagle store identifier |
| sku | VARCHAR(20) | Stock keeping unit |
| week_start | DATE | Monday starting the inventory week |
| units_on_hand | INT | Units available at the weekly snapshot |