Three-Month Moving Averages by User | Dataford Interview Questions

Your question is Three-Month Moving Averages by User. Start with the requirements and the two tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem

Arity analyzes monthly mileage from connected-vehicle telemetry to support driver risk and usage insights. Write a PostgreSQL query that calculates a running three-month moving average of mileage for each active driver.

Requirements

Include only drivers whose is_active value is TRUE.
Return the driver ID, calendar month, reported mileage, and moving average.
Partition calculations by user_id and order each driver's records chronologically.
Use the current month and up to the two preceding monthly records, rounding the average to two decimal places. NULL mileage values should be ignored by AVG.

Assume arity_monthly_mileage contains at most one row per driver per calendar month.

Schema

arity_users

Column	Type	Description
user_idPK	INTEGER	Unique driver identifier
user_name	VARCHAR(100)	Driver display name
is_active	BOOLEAN	Whether the driver is currently active

arity_monthly_mileage

Column	Type	Description
mileage_idPK	INTEGER	Unique mileage record identifier
user_id	INTEGER	References arity_users.user_id
month_start	DATE	First calendar day of the measurement month
miles_driven	NUMERIC(10,1)	Total miles driven during the month