Store KPI SQL Analysis

MediumSQL · PostgreSQL

Write a SQL query to analyze Mars store performance by joining sales, inventory, and labor tables, and return the key KPIs by store and week.

Use the provided tables and include weeks represented in any source table. Missing source values should not remove a store-week from the results.

Output

One row per store and week, with store_id, week_start, net_sales, units_sold, avg_inventory_units, labor_hours, labor_cost, and sales_per_labor_hour.
Include all store-weeks present in any source table, treating missing additive measures as zero where appropriate and returning NULL when labor hours are zero or unavailable.
Order by store_id, then week_start.

sales

Column	Type	Description
sale_idPK	INT	Unique sales transaction identifier
store_id	INT	Mars store identifier
sale_date	DATE	Transaction date
units_sold	INT	Units sold in the transaction
net_sales	DECIMAL(12,2)	Net sales amount after discounts and returns

inventory

Column	Type	Description
inventory_idPK	INT	Unique inventory snapshot identifier
store_id	INT	Mars store identifier
inventory_date	DATE	Inventory snapshot date
units_on_hand	INT	Units available at the snapshot time
inventory_value	DECIMAL(12,2)	Estimated inventory value

labor

Column	Type	Description
labor_idPK	INT	Unique labor record identifier
store_id	INT	Mars store identifier
labor_date	DATE	Shift or labor record date
labor_hours	DECIMAL(8,2)	Hours worked
labor_cost	DECIMAL(12,2)	Labor cost for the record

Tablessalesinventorylabor