Write a SQL query to analyze store performance by joining sales, labor, and inventory tables and summarizing key operational metrics by store and week for Walgreens.
Return all store-week combinations found in any source table. Use the available data to calculate the requested metrics and handle missing source records appropriately.
store_id, then week_start.store_id, week_start, sales_units, sales_revenue, labor_hours, labor_cost, avg_inventory_units, stockout_days, and revenue_per_labor_hour.| Column | Type | Description |
|---|---|---|
| sale_idPK | INT | Unique sales record identifier |
| store_id | INT | Walgreens store identifier |
| week_start | DATE | Start date of the reporting week |
| units_sold | INT | Units sold, including negative return quantities |
| sales_amount | DECIMAL(12,2) | Sales revenue for the record |
| Column | Type | Description |
|---|---|---|
| labor_idPK | INT | Unique labor record identifier |
| store_id | INT | Walgreens store identifier |
| week_start | DATE | Start date of the reporting week |
| hours_worked | DECIMAL(8,2) | Employee hours worked |
| labor_cost | DECIMAL(12,2) | Labor cost for the record |
| Column | Type | Description |
|---|---|---|
| inventory_idPK | INT | Unique inventory observation identifier |
| store_id | INT | Walgreens store identifier |
| week_start | DATE | Start date of the reporting week |
| on_hand_units | INT | Observed units available in inventory |