Objective

Write a SQL query to find the top 3 highest-paid employees within each department.

Table Schema:

• employee

• department

Task Requirements:

• Join employee and department tables
• Identify the top 3 highest-paid employees in each department
• Order by department name (ascending), then salary (descending), then employee name (ascending)
  
  

Output columns: employee_name, department_name, salary

SELECT 
    employee_name,
    department_name,
    salary
FROM (
    SELECT 
        e.employee_name,
        d.department_name,
        e.salary,
        ROW_NUMBER() OVER (PARTITION BY d.department_name ORDER BY e.salary DESC, e.employee_name ASC) AS salary_rank
    FROM employee e
    INNER JOIN department d ON e.department_id = d.department_id
) AS ranked_employees
WHERE salary_rank <= 3
ORDER BY department_name ASC, salary DESC, employee_name ASC;

Explanation

Step 1: Joining Employee and Department Tables

FROM employee e
INNER JOIN department d ON e.department_id = d.department_id

Joins employee and department tables to associate each employee with their department name.

Step 2: Ranking Employees Within Departments

ROW_NUMBER() OVER (PARTITION BY d.department_name ORDER BY e.salary DESC, e.employee_name ASC) AS salary_rank

ROW_NUMBER() assigns unique sequential ranks per department. Ranks by salary descending; ties broken alphabetically by employee name. Guarantees exactly 3 per department.

Step 3: Filtering for Top 3

WHERE salary_rank <= 3

Keeps only employees with ranks 1, 2, or 3 per department.

Step 4: Ordering the Final Output

ORDER BY department_name ASC, salary DESC, employee_name ASC

Sorts by department alphabetically, then by salary descending within each department, then by employee name for ties.