SQL for Throughput and Labor Cost | Dataford Interview Questions

Write a SQL query to join staffing, inventory, and sales tables to identify sites with declining throughput and rising labor cost.

Calculate monthly throughput as processed units divided by average ending inventory. Compare each site's current month with its immediately preceding month, excluding comparisons with missing prior values.

Output

One row per site and qualifying current month
Columns: site_id, current_month, prior_throughput, current_throughput, prior_labor_cost, current_labor_cost
Include only sites where throughput decreased and labor cost increased
Order by site_id, then current_month

Schema

staffing

Column	Type	Description
staffing_idPK	INT	Staffing record identifier
site_id	VARCHAR(20)	Staffing site identifier
month	DATE	First day of the reporting month
labor_hours	DECIMAL(10,2)	Total labor hours recorded
labor_cost	DECIMAL(12,2)	Total labor cost

inventory

Column	Type	Description
inventory_idPK	INT	Inventory record identifier
site_id	VARCHAR(20)	Inventory site identifier
month	DATE	First day of the reporting month
ending_inventory_units	INT	Units remaining at the period end

sales

Column	Type	Description
sale_idPK	INT	Sales record identifier
site_id	VARCHAR(20)	Sales site identifier
month	DATE	First day of the reporting month
units_processed	INT	Units processed during the month

Your question is SQL for Throughput and Labor Cost. 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