SQL Joins and Time-Window Aggregations | Dataford Interview Questions

Write a SQL query to join multiple tables and aggregate performance metrics over a specific time window.

Use the provided site, asset, and performance record tables. Analyze records from January 1, 2025 through March 31, 2025, inclusive. Include sites without qualifying performance records.

Output

One row per site.
Return site_id, site_name, asset_count, avg_availability_pct, total_production_units, and avg_energy_kwh.
Count all assets assigned to each site, but calculate metrics only from records in the time window.
Preserve NULL for averages with no qualifying values, use zero for total production with no qualifying records, and order by site_id ascending.

Schema

sites

Column	Type	Description
site_idPK	INT	Unique site identifier
site_name	VARCHAR(100)	Site name
region	VARCHAR(50)	Geographic region

assets

Column	Type	Description
asset_idPK	INT	Unique asset identifier
site_id	INT	Site assigned to the asset
asset_name	VARCHAR(100)	Asset name
asset_type	VARCHAR(50)	Asset category

performance_records

Column	Type	Description
record_idPK	INT	Unique performance record identifier
asset_id	INT	Asset measured by the record
measured_at	DATE	Date of the performance measurement
availability_pct	DECIMAL(5,2)	Asset availability percentage
production_units	DECIMAL(12,2)	Units produced during the measurement period
energy_kwh	DECIMAL(12,2)	Energy consumed in kilowatt-hours

Your question is SQL Joins and Time-Window Aggregations. 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