Write a SQL query to analyze store performance by joining sales, inventory, and staffing data, and return the key business KPIs by week at Staples India.
Use the supplied tables and calculate metrics at store-week grain, preserving sales weeks even when supporting data is unavailable.
Output
- Return
store_code, week_start, weekly_revenue, units_sold, average_inventory, stockout_days, labor_hours, sales_per_labor_hour, and revenue_change_pct.
- Include one row per store and week represented in sales, with unavailable supporting metrics as
NULL where appropriate.
- Order by
store_code, then week_start ascending.
Output details
- Treat each week as starting on Monday.
- Round
sales_per_labor_hour to two decimal places.
- Calculate
revenue_change_pct as the percentage change from the previous sales week for that store, rounded to two decimals.
- Return
revenue_change_pct as NULL for a store's first sales week or when prior-week revenue is zero.
- Return
stockout_days as 0 when no inventory data exists for that store-week, while leaving other unavailable supporting metrics as NULL.
