SQL Window Functions for Trends | Dataford Interview Questions

Duke University research administrators need a monthly view of expenditures recorded for active research grants. Write a PostgreSQL query that summarizes expenditures from January through March 2025 and calculates running totals and moving averages for each grant.

Requirements

Join grants to expenditures and include only active grants with expenditures in the requested date range.
Aggregate multiple expenditure records into one row per grant and calendar month.
Calculate a cumulative running total and a three-month moving average using window functions, ordered chronologically within each grant.
Return results ordered by grant and month. Preserve NULL monthly totals when a month contains only NULL expenditure amounts.

Schema

grants

Column	Type	Description
grant_idPK	INTEGER	Unique research grant identifier
grant_name	VARCHAR(150)	Research grant title
principal_investigator	VARCHAR(120)	Principal investigator responsible for the grant
status	VARCHAR(20)	Current grant status

grant_expenditures

Column	Type	Description
expenditure_idPK	INTEGER	Unique expenditure record identifier
grant_id	INTEGER	Grant associated with the expenditure
expenditure_date	DATE	Date the expenditure was recorded
amount	NUMERIC(12,2)	Expenditure amount in US dollars

Your question is SQL Window Functions for Trends. Start with the requirements and the two 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