SQL: Sales and Cost by Store | Dataford Interview Questions

Write a SQL query to calculate weekly sales, labor cost, and food cost by store, and flag stores whose labor percentage exceeds a threshold at SSP Group.

Use the week from 2025-01-06 through 2025-01-12 and a labor threshold of 30%.

Output

One row per SSP Group store, including stores with no activity.
Return store_id, store_name, week_start, weekly_sales, weekly_labor_cost, weekly_food_cost, labor_percentage, and labor_over_threshold.
Order by store_id ascending.

Schema

stores

Column	Type	Description
store_idPK	INT	Unique SSP Group store identifier
store_name	VARCHAR(100)	SSP Group store name

store_daily_financials

Column	Type	Description
financial_idPK	INT	Unique daily financial record identifier
store_id	INT	Store associated with the daily financial record
business_date	DATE	Trading date for the financial record
sales_amount	NUMERIC(12,2)	Daily sales amount
labor_cost	NUMERIC(12,2)	Daily labor cost
food_cost	NUMERIC(12,2)	Daily food cost

Your question is SQL: Sales and Cost by Store. Start with the requirements and the two 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