Year-over-Year Grant Funding Query | Dataford Interview Questions

The University of Colorado's research administration team needs an annual view of awarded grant funding by department. Write a PostgreSQL query that calculates year-over-year funding growth for each department and award year.

Requirements

Include only grants with award_status = 'Awarded'.
Aggregate awarded amounts by department and calendar year.
Compare each year's total with the previous year's total using a join or equivalent SQL technique.
Return NULL for growth when no prior-year total exists or the prior-year total is zero. Round percentage growth to two decimal places.

Schema

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(100)	University department name

grant_awards

Column	Type	Description
grant_idPK	INT	Unique grant award identifier
department_id	INT	Department receiving the award
award_date	DATE	Date the grant was awarded
amount	NUMERIC(12,2)	Awarded funding amount
award_status	VARCHAR(20)	Current award status

Your question is Year-over-Year Grant Funding Query. 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