Write a SQL query to analyze weekly store performance by region, joining sales, inventory, and labor tables to identify stores with declining sales and low in-stock rates at L.L. Bean.
Treat declining sales as a current weekly total below the prior week and low in-stock as a rate below 90%. Include only weeks with a prior sales week.
region, store_id, week_start, weekly_sales, prior_week_sales, sales_decline_pct, in_stock_rate, and labor_hoursregion, store_id, and week_start ascending| Column | Type | Description |
|---|---|---|
| sale_idPK | INT | Unique sales transaction identifier |
| store_id | VARCHAR(10) | L.L. Bean store identifier |
| region | VARCHAR(50) | Store operating region |
| week_start | DATE | Start date of the sales week |
| net_sales | DECIMAL(12,2) | Net sales amount for the transaction |
| Column | Type | Description |
|---|---|---|
| store_id | VARCHAR(10) | L.L. Bean store identifier |
| region | VARCHAR(50) | Store operating region |
| week_start | DATE | Start date of the inventory week |
| units_in_stock | INT | Units available during the week |
| units_expected | INT | Units expected to be available during the week |
| Column | Type | Description |
|---|---|---|
| labor_idPK | INT | Unique labor record identifier |
| store_id | VARCHAR(10) | L.L. Bean store identifier |
| region | VARCHAR(50) | Store operating region |
| week_start | DATE | Start date of the labor week |
| labor_hours | DECIMAL(8,2) | Labor hours recorded for the week |