Window Function for Employee Ranking | Dataford Interview Questions

How would you use a window function to rank employees by performance within each department at Kpmg Us?

Use the provided employee and performance review data. Calculate each active employee's average performance score from reviews dated in 2025, excluding employees without a department or valid score.

Output

One row per qualifying employee with department_name, employee_name, average_performance, and performance_rank.
Tied average scores must receive the same rank.
Order by department, rank ascending, and employee ID ascending.

Schema

employees

Column	Type	Description
employee_idPK	INT	Unique employee identifier
employee_name	VARCHAR(100)	Employee full name
department_name	VARCHAR(50)	Employee department
is_active	BOOLEAN	Whether the employee is currently active

performance_reviews

Column	Type	Description
review_idPK	INT	Unique performance review identifier
employee_id	INT	Employee associated with the review
review_date	DATE	Date the review was recorded
performance_score	DECIMAL(5,2)	Performance score for the review

Your question is Window Function for Employee Ranking. Start with the requirements and the two tables in the Question tab.

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

Problem