Weekly Sales and Margin SQL | Dataford Interview Questions

Write a SQL query to calculate weekly sales, labor cost, and margin by McDonald's store using window functions or CTEs.

Use the provided McDonald's store, sales, and labor data. Treat weeks as Monday through Sunday and include every store-week with sales or labor activity. Missing sales or labor amounts should be treated as zero.

Output

One row per store and week, excluding weeks with no activity
Columns: store_id, store_name, week_start, weekly_sales, labor_cost, margin, prior_week_margin, and margin_change
Margin equals weekly sales minus labor cost; week-over-week fields compare rows within each store
Order by store_id, then week_start ascending

Schema

stores

Column	Type	Description
store_idPK	INT	Unique McDonald's store identifier
store_name	VARCHAR(100)	Store display name
region	VARCHAR(50)	Operating region

store_sales

Column	Type	Description
sale_idPK	INT	Unique sales record identifier
store_id	INT	Referenced McDonald's store
sale_date	DATE	Date of the sales record
sales_amount	NUMERIC(12,2)	Sales amount for the record

store_labor

Column	Type	Description
labor_idPK	INT	Unique labor record identifier
store_id	INT	Referenced McDonald's store
labor_date	DATE	Date of the labor record
labor_cost	NUMERIC(12,2)	Labor cost for the record

Your question is Weekly Sales and Margin SQL. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem