Business Context
CVS Health operations leaders need a store-level view of sales, profitability, and inventory health across regions. The analysis should preserve stores with no sales or inventory snapshot so operational gaps remain visible.
Task
Write a PostgreSQL query for Q1 2025 that combines stores, sales, and inventory data without double-counting sales because of multiple inventory snapshots.
Requirements
- Aggregate net sales and cost by store, then calculate margin percentage.
- Select each store's most recent inventory snapshot in the period using a window function.
- Calculate regional average sales and margin, plus each store's sales rank within its region.
- Mark a store as
UNDERPERFORMING when sales are below 75% of its regional average, margin is below the regional average, or latest inventory units equal zero. Otherwise mark it PERFORMING.
- Retain stores with no sales and order results by region, sales rank, and store ID.
Representative Data
Output details
- Return columns in this order:
region, store_id, store_name, total_sales, total_cost, margin_pct, latest_inventory_units, regional_avg_sales, regional_avg_margin, sales_rank, performance_status.
- Filter Q1 2025 inclusively from
DATE '2025-01-01' through exclusively before DATE '2025-04-01'.
- Sort
region, sales_rank, and store_id ascending; assign tied sales equal ranks with skipped subsequent rank numbers.
- Break equal inventory snapshot dates by descending
inventory_id when selecting the latest snapshot.
- Round
margin_pct and regional_avg_margin to four decimal places, and regional_avg_sales to two decimal places.
- Treat missing sales and cost as zero, preserve null margins and inventory, ignore null margins in averages, and let null comparisons remain non-underperforming.
