Running Totals and Moving Averages | Dataford Interview Questions

Your question is Running Totals and Moving Averages. 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

TransUnion needs a customer-level view of credit utilization trends for analytics and risk monitoring. Write a PostgreSQL query that aggregates account snapshots by month, then uses window functions to calculate cumulative and rolling utilization metrics.

Requirements

Join customer records to credit utilization snapshots and exclude snapshots with a missing balance or credit limit.
Calculate monthly utilization as total balance divided by total credit limit, multiplied by 100.
Calculate a running utilization total and a three-month moving average for each customer, ordered chronologically.
Return results ordered by customer ID and month. The moving average should use the current row and up to the two preceding available monthly rows.

Schema

customers

Column	Type	Description
customer_idPK	INTEGER	Unique customer identifier
customer_name	VARCHAR(100)	Customer display name

credit_utilization_snapshots

Column	Type	Description
snapshot_idPK	INTEGER	Unique snapshot identifier
customer_id	INTEGER	Customer associated with the snapshot
snapshot_date	DATE	Date when the balance and limit were recorded
balance	NUMERIC(12,2)	Outstanding revolving balance
credit_limit	NUMERIC(12,2)	Total reported credit limit