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.
Output details
- Return columns in this order:
candidate_name, application_id, position_title, average_score, feedback_count, application_rank, review_status.
- Round
average_score to two decimal places and leave it NULL when no scored feedback exists.
- Use
0 for feedback_count when an application has no feedback.
- Rank
NULL average scores last, then break remaining ties by application_id ascending.
- Assign
Needs review for NULL averages, Recommended for averages at least 80, and Review otherwise.
- Sort final results by
candidate_id ascending, then application_rank ascending.
