YoY Research Grants by Department | Dataford Interview Questions

The University of Chicago research administration team needs to monitor annual awarded research funding by department. Write a PostgreSQL query that calculates year-over-year growth for every department and fiscal year with at least one awarded grant.

Requirements

Extract the calendar year from award_date and aggregate awarded grant amounts by department and year.
Compare each year's total with the same department's previous calendar year using a LEFT JOIN.
Return NULL for growth when no prior-year total exists or when the prior-year total is zero.
Report growth as a percentage rounded to two decimal places, ordered by department and year.

Schema

departments

Column	Type	Description
department_idPK	INTEGER	Unique department identifier
department_name	VARCHAR(100)	Department name
school_name	VARCHAR(100)	Associated school or division

research_grants

Column	Type	Description
grant_idPK	INTEGER	Unique grant identifier
department_id	INTEGER	Department associated with the grant
award_date	DATE	Date the grant was awarded
amount	NUMERIC(12,2)	Grant funding amount
grant_status	VARCHAR(20)	Current grant status

Your question is YoY Research Grants by Department. 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