Your question is Rank Departments with Window Functions. Start with the requirements and the three 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.
The University of Chicago's research administration team wants to compare external research funding growth across academic departments. Write a PostgreSQL query that ranks eligible departments by the percentage change in awarded external grant funding from fiscal year 2023 to fiscal year 2024.
status = 'Awarded' and funders marked as external.| Column | Type | Description |
|---|---|---|
| department_idPK | INT | Unique department identifier |
| department_name | VARCHAR(120) | University of Chicago department name |
| Column | Type | Description |
|---|---|---|
| funder_idPK | INT | Unique funder identifier |
| funder_name | VARCHAR(120) | Funding organization name |
| is_external | BOOLEAN | Whether the funder is external to UChicago |
| Column | Type | Description |
|---|---|---|
| grant_idPK | INT | Unique grant identifier |
| department_id | INT | Department receiving the grant |
| funder_id | INT | Funder issuing the grant |
| fiscal_year | INT | Fiscal year associated with the grant |
| award_amount | NUMERIC(14,2) | Grant award amount in US dollars |
| status | VARCHAR(30) | Grant lifecycle status |
| grant_title | VARCHAR(180) | Short grant title |