SQL Joins and Window Functions

MediumSQL · PostgreSQL

Problem

Business Context

Steven Douglas Associates wants a recruiter-facing view of candidate applications and interview performance. Applications without scored feedback must remain visible so recruiters can identify records needing review.

Task

Write a PostgreSQL query that joins candidates, applications, and interview feedback to rank each candidate's qualifying applications.

Requirements

Include applications submitted during the first quarter of 2025, excluding withdrawn applications.
Calculate each application's average interview score and feedback count.
Rank applications per candidate by average score descending, then submission date descending, using ROW_NUMBER().
Label applications as Recommended, Review, or Needs review with CASE.

Schema

candidates

Column	Type	Description
candidate_idPK	INT	Unique candidate identifier
candidate_name	VARCHAR(100)	Candidate's full name
email	VARCHAR(150)	Candidate email address

applications

Column	Type	Description
application_idPK	INT	Unique application identifier
candidate_id	INT	References candidates.candidate_id
position_title	VARCHAR(150)	Position associated with the application
submitted_at	DATE	Date the application was submitted
status	VARCHAR(30)	Application workflow status

interview_feedback

Column	Type	Description
feedback_idPK	INT	Unique feedback identifier
application_id	INT	References applications.application_id
interviewer_name	VARCHAR(100)	Interviewer providing feedback
score	NUMERIC(5,2)	Interview score from 0 to 100
recommendation	VARCHAR(30)	Interviewer's recommendation

Tablescandidatesapplicationsinterview_feedback