Top Performers by Department Query

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Top Performers by Department Query. Start with the requirements and the four tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

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

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results

Top Performers by Department Query

MediumSQL · PostgreSQL00:00

Practice interviewer

In session

5 left

00:00

Your question is Top Performers by Department Query. Start with the requirements and the four tables on the right.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

You need to log in / sign up to run or submit.

Problem

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

Your solutionPostgreSQL

You need to log in / sign up to run or submit.

Run a query to see results