Your question is SQL: Store Performance and Margin. Start with the requirements and the three tables on the right.
Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.
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.
Write a PostgreSQL query for Q1 2025 that combines stores, sales, and inventory data without double-counting sales because of multiple inventory snapshots.
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.| Column | Type | Description |
|---|---|---|
| store_idPK | INT | Unique CVS Health store identifier |
| store_name | VARCHAR(100) | Store display name |
| region | VARCHAR(50) | Operating region |
| 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 |
| 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 |