Top Performers by Department Query

MediumSQL · PostgreSQL

Problem

Unum's Data Science team wants a department-level view of its strongest active employees. Write a PostgreSQL query that combines employee, review, and performance outcome data to rank performers within each department.

Requirements

Include only active employees assigned to a department and having at least one non-null review score.
Calculate each employee's average review score and average performance outcome score.
Calculate a performance score using 70% of the average review score and 30% of the average outcome score. Treat missing outcome scores as zero.
Rank employees independently within each department and return the top three, using employee ID as a deterministic tie-breaker.
Return department name, employee name, both averages, the calculated performance score, and the employee's rank.

Schema

departments

Column	Type	Description
department_idPK	INTEGER	Unique department identifier
department_name	VARCHAR(100)	Department name

employees

Column	Type	Description
employee_idPK	INTEGER	Unique employee identifier
employee_name	VARCHAR(100)	Employee's full name
department_id	INTEGER	Assigned department identifier
employment_status	VARCHAR(20)	Current employment status

performance_reviews

Column	Type	Description
review_idPK	INTEGER	Unique review identifier
employee_id	INTEGER	Employee being reviewed
review_period	VARCHAR(20)	Review period label
review_score	NUMERIC(5,2)	Review score

performance_results

Column	Type	Description
result_idPK	INTEGER	Unique outcome record identifier
employee_id	INTEGER	Employee associated with the outcome
performance_period	VARCHAR(20)	Outcome period label
outcome_score	NUMERIC(5,2)	Measured performance outcome score

Tablesdepartmentsemployeesperformance_reviewsperformance_results