SQL for Weekly Operational Review

MediumSQL · PostgreSQL

Problem

Write an SQL query to analyze store performance at Albertsons by joining sales, labor, and inventory tables and summarizing key operational metrics by week.

Use the supplied tables and include all sales store-weeks in the requested date range. Treat missing labor or inventory records as zero for numeric totals, while preserving meaningful NULL inventory averages when no usable inventory quantity exists.

Output

One row per store and Monday-based week beginning January 1, 2025 through January 14, 2025
Columns: store_id, week_start, total_sales, total_units_sold, total_labor_hours, total_labor_cost, avg_inventory_units, stockout_days
Order by store_id, then week_start ascending

Schema

sales

Column	Type	Description
sale_idPK	INT	Unique sales record identifier
store_id	INT	Albertsons store identifier
sale_date	DATE	Date of the sales record
net_sales	NUMERIC(12,2)	Net sales amount
units_sold	INT	Units sold in the record

labor

Column	Type	Description
labor_idPK	INT	Unique labor record identifier
store_id	INT	Albertsons store identifier
labor_date	DATE	Date of the labor record
labor_hours	NUMERIC(8,2)	Labor hours worked
labor_cost	NUMERIC(12,2)	Labor cost for the record

inventory

Column	Type	Description
inventory_idPK	INT	Unique inventory record identifier
store_id	INT	Albertsons store identifier
inventory_date	DATE	Date of the inventory snapshot
inventory_units	INT	Units available in inventory
stockout_flag	BOOLEAN	Whether the store experienced a stockout

Tablessaleslaborinventory