Window Functions for Analytics | Dataford Interview Questions

Write a complex SQL query using window functions to calculate running totals and moving averages across multi-million-row partitioned tables.

Use the provided measurement, entity, and region tables. Restrict measurements to January 2025 and calculate daily totals before producing the analytical results.

Output

One row per entity and measurement date, with entity_id, entity_name, region_name, measured_at, daily_total, running_total, and moving_avg_3_rows.
Include only entities with measurements in the requested period. The moving average uses the current row and up to two preceding dates for the same entity.
Order by region_name, entity_id, and measured_at ascending.

Schema

measurement_events

Column	Type	Description
measurement_idPK	BIGINT	Unique measurement identifier
entity_id	INT	Measured entity identifier
measured_at	DATE	Measurement date and partition key
amount	NUMERIC(12,2)	Measured numeric value

entities

Column	Type	Description
entity_idPK	INT	Entity identifier
entity_name	VARCHAR(100)	Entity display name
region_id	INT	Associated region identifier

regions

Column	Type	Description
region_idPK	INT	Region identifier
region_name	VARCHAR(100)	Region display name

Your question is Window Functions for Analytics. 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