Your question is Rank Applicants 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.
Stanford University’s research administration team wants to compare grant applications within each academic department. Write a PostgreSQL query that calculates each application’s average review score and ranks applications within its department.
RANK() partitioned by department, ordering higher average scores first. Applications without scores should appear after scored applications.| Column | Type | Description |
|---|---|---|
| department_idPK | INTEGER | Primary key for the department |
| department_name | VARCHAR(100) | Stanford academic department name |
| Column | Type | Description |
|---|---|---|
| application_idPK | INTEGER | Primary key for the grant application |
| applicant_name | VARCHAR(120) | Applicant or principal investigator |
| department_id | INTEGER | References departments.department_id |
| proposal_title | VARCHAR(200) | Grant proposal title |
| Column | Type | Description |
|---|---|---|
| review_idPK | INTEGER | Primary key for the review |
| application_id | INTEGER | References grant_applications.application_id |
| reviewer_name | VARCHAR(120) | Reviewer name |
| score | NUMERIC(5,2) | Review score from 0 to 100 |