Top Candidates with Complex Joins

HardSQL · PostgreSQL

Problem

Phenom's recruiting analytics team wants to identify top-performing candidates across its candidate experience and recruiting workflows. Write a PostgreSQL query that ranks eligible candidates using application outcomes and completed interview scores.

Requirements

Consider applications submitted from January 1, 2025 through June 30, 2025.
Include candidates with at least two completed interviews, an average completed interview score of at least 75, and at least one application with status advanced or hired.
Calculate each candidate's application count, completed interview count, average interview score, and advanced application count.
Rank candidates by average interview score in descending order and return the top three ranks, including ties.

Schema

candidates

Column	Type	Description
candidate_idPK	INT	Unique candidate identifier
full_name	VARCHAR(100)	Candidate full name
email	VARCHAR(150)	Candidate email address
current_title	VARCHAR(120)	Candidate's current job title

applications

Column	Type	Description
application_idPK	INT	Unique application identifier
candidate_id	INT	References candidates.candidate_id
applied_at	DATE	Date the application was submitted
status	VARCHAR(30)	Current application status
source	VARCHAR(50)	Application source

interviews

Column	Type	Description
interview_idPK	INT	Unique interview identifier
application_id	INT	References applications.application_id
interview_status	VARCHAR(30)	Interview completion status
score	INT	Interview score from 0 to 100
interviewer	VARCHAR(100)	Interviewer name

Tablescandidatesapplicationsinterviews