SQL: Store Performance and Margin

HardSQL · PostgreSQL

Problem

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

Schema

stores

Column	Type	Description
store_idPK	INT	Unique CVS Health store identifier
store_name	VARCHAR(100)	Store display name
region	VARCHAR(50)	Operating region

sales

Column	Type	Description
sale_idPK	INT	Unique sales transaction identifier
store_id	INT	Store associated with the transaction
sale_date	DATE	Transaction date
net_sales	NUMERIC(12,2)	Net transaction sales amount
cost	NUMERIC(12,2)	Cost of goods sold

inventory

Column	Type	Description
inventory_idPK	INT	Unique inventory snapshot identifier
store_id	INT	Store associated with the snapshot
snapshot_date	DATE	Date inventory was measured
inventory_units	INT	Units available at the snapshot

Tablesstoressalesinventory