SQL for Cost Variances and Labor Hours

MediumSQL · PostgreSQL

Problem

Burns & McDonnell project controls needs a subcontractor-level view of financial and labor performance across large projects. Write a PostgreSQL query that summarizes eligible project cost records.

Requirements

Use a CTE to identify projects with a contract value greater than $1,000,000. Exclude projects at exactly $1,000,000 and projects with a NULL contract value.
Join the eligible projects to project_costs and subcontractors.
Group results by subcontractor ID and name.
Return total cost variance, calculated as actual cost minus budgeted cost, and total labor hours. Treat NULL cost or labor values as zero, and order by subcontractor ID ascending.

Schema

projects

Column	Type	Description
project_idPK	INT	Unique project identifier
project_name	VARCHAR(100)	Project name
contract_value	DECIMAL(14,2)	Approved project contract value
status	VARCHAR(20)	Current project status

project_costs

Column	Type	Description
cost_idPK	INT	Unique cost record identifier
project_id	INT	References projects.project_id
subcontractor_id	INT	References subcontractors.subcontractor_id
budgeted_cost	DECIMAL(12,2)	Budgeted amount for the cost record
actual_cost	DECIMAL(12,2)	Actual incurred amount
labor_hours	DECIMAL(10,2)	Reported labor hours

subcontractors

Column	Type	Description
subcontractor_idPK	INT	Unique subcontractor identifier
subcontractor_name	VARCHAR(100)	Subcontractor legal or display name

Tablesprojectsproject_costssubcontractors