Techcorp mid Window FunctionsROW_NUMBERRANKDENSE_RANKLAGLEADAVG OVERORDER BY

For each employee, display their name, department, latest performance rating, the previous performance rating (to track improvement), their ranking within the department, and whether their latest rating is above or below their department's average. Sort by department and ranking.

Techcorp · Performance Ranking — practice this real-world SQL scenario live in your browser.

📖 Story Techcorp · Performance Ranking

Friday morning at TechCorp's HR department. The HR Manager is preparing the quarterly performance review and compensation analysis. She needs to identify top performers within each department, track their performance improvement over time, and compare individual performance against department averages. Using window functions, she can rank employees, compare their current ratings with previous reviews, and identify those performing above their department's average.

🎯 Your Mission

📋 Table Structure

🗂 employees

employee_id INTEGER 1001

employee_name TEXT Rajesh Kumar

department TEXT Sales

hire_date TEXT 2020-03-15

salary INTEGER 45000

🗂 performance

performance_id INTEGER 1

employee_id INTEGER 1001

review_date TEXT 2024-01-15

rating DECIMAL 3.5

⚡ Step-by-Step Walkthrough

Get the latest performance rating for each employee

query.sql

WITH latest_ratings AS (
  SELECT e.employee_id,
         e.employee_name,
         e.department,
         p.rating AS latest_rating,
         p.review_date,
         ROW_NUMBER() OVER (PARTITION BY e.employee_id ORDER BY p.review_date DESC) AS rn
  FROM employees e
  JOIN performance p ON e.employee_id = p.employee_id
)
SELECT employee_id,
       employee_name,
       department,
       latest_rating,
       review_date
FROM latest_ratings
WHERE rn = 1;

💡 Explanation

The employees table stores employee information, while the performance table contains multiple performance reviews for each employee. To get the latest rating for each employee, we use a window function approach.
ROW_NUMBER() OVER (PARTITION BY e.employee_id ORDER BY p.review_date DESC) assigns a unique number to each performance review within an employee partition, ordered by review date in descending order. The most recent review gets rn=1.
PARTITION BY e.employee_id divides the performance records into separate groups for each employee. Each partition is ordered independently.
ORDER BY p.review_date DESC ensures that within each employee's partition, the most recent review gets row number 1.
WHERE rn = 1 filters to keep only the latest performance record for each employee.
This step prepares the foundation for the next steps where we'll compare current ratings with previous ratings and calculate rankings.

Get both latest and previous performance ratings using LAG window function

query.sql

WITH all_ratings AS (
  SELECT e.employee_id,
         e.employee_name,
         e.department,
         p.rating,
         p.review_date,
         LAG(p.rating) OVER (PARTITION BY e.employee_id ORDER BY p.review_date) AS previous_rating,
         ROW_NUMBER() OVER (PARTITION BY e.employee_id ORDER BY p.review_date DESC) AS rn
  FROM employees e
  JOIN performance p ON e.employee_id = p.employee_id
)
SELECT employee_id,
       employee_name,
       department,
       rating AS latest_rating,
       previous_rating,
       ROUND(rating - COALESCE(previous_rating, 0), 2) AS improvement
FROM all_ratings
WHERE rn = 1;

💡 Explanation

LAG() is a window function that accesses the previous row's value within the same partition. In this case, it retrieves the rating from the employee's previous performance review.
LAG(p.rating) OVER (PARTITION BY e.employee_id ORDER BY p.review_date) gets the rating from the chronologically previous review for each employee. For the first review of an employee, LAG returns NULL.
By using PARTITION BY e.employee_id and ORDER BY p.review_date (in ascending order, not descending), we ensure that LAG correctly identifies the previous review in chronological order.
COALESCE(previous_rating, 0) handles the case where there is no previous review (first review), replacing NULL with 0 for calculation purposes.
ROUND(rating - COALESCE(previous_rating, 0), 2) calculates the improvement: the difference between the latest rating and the previous rating.
WHERE rn = 1 filters to keep only the latest performance review for each employee, now enriched with previous rating and improvement metrics.

Rank employees within their department and compare against department average

query.sql

WITH employee_performance AS (
  SELECT e.employee_id,
         e.employee_name,
         e.department,
         p.rating AS latest_rating,
         LAG(p.rating) OVER (PARTITION BY e.employee_id ORDER BY p.review_date) AS previous_rating,
         ROW_NUMBER() OVER (PARTITION BY e.employee_id ORDER BY p.review_date DESC) AS rn,
         RANK() OVER (PARTITION BY e.department ORDER BY p.rating DESC) AS dept_rank,
         ROUND(AVG(p.rating) OVER (PARTITION BY e.department), 2) AS dept_avg_rating
  FROM employees e
  JOIN performance p ON e.employee_id = p.employee_id
)
SELECT employee_id,
       employee_name,
       department,
       latest_rating,
       COALESCE(previous_rating, 0) AS previous_rating,
       ROUND(latest_rating - COALESCE(previous_rating, 0), 2) AS improvement,
       dept_rank,
       dept_avg_rating,
       CASE WHEN latest_rating > dept_avg_rating THEN 'Above Average' 
            WHEN latest_rating = dept_avg_rating THEN 'At Average'
            ELSE 'Below Average' 
       END AS performance_vs_dept
FROM employee_performance
WHERE rn = 1
ORDER BY department, dept_rank;

💡 Explanation

This step combines multiple window functions to provide a comprehensive performance analysis for each employee.
RANK() OVER (PARTITION BY e.department ORDER BY p.rating DESC) ranks employees within their department based on their latest rating. Employees with the same rating get the same rank, and the next rank skips accordingly.
AVG(p.rating) OVER (PARTITION BY e.department) calculates the average rating for each department. This window function operates on all rows within each department partition.
The window frame includes all rows in the partition by default, so this calculates the average of all performance ratings in that department.
LAG() is used again to get the previous rating for improvement tracking.
The CASE statement compares each employee's latest rating against their department's average to classify them as 'Above Average', 'At Average', or 'Below Average'.
WHERE rn = 1 ensures we're working with the latest performance record for each employee.
ORDER BY department, dept_rank organizes the output by department and then by ranking within that department.

▶ Practice this live ← Browse all questions