Rank Applicants With Window Functions | Dataford Interview Questions

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.

Requirements

Join applications to departments and review records.
Calculate the average non-NULL review score for every application, including applications without reviews.
Use RANK() partitioned by department, ordering higher average scores first. Applications without scores should appear after scored applications.
Return the results ordered by department, rank, and application ID.

Schema

departments

Column	Type	Description
department_idPK	INTEGER	Primary key for the department
department_name	VARCHAR(100)	Stanford academic department name

grant_applications

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

grant_reviews

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

Your question is Rank Applicants With Window Functions. Start with the requirements and the three 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