Lifted from: Timmy Kurian’s Linkedin

Problem:

Given a table “employees” with columns “employee_id”, “first_name”, “last_name”, “department_id”, and “salary”, return the top 3 employees with the highest salaries for each department.

Sample Tables (CREATE + INSERT):

CREATE TABLE employees (
 employee_id INT,
 first_name VARCHAR(50),
 last_name VARCHAR(50),
 department_id INT,
 salary INT
);
INSERT INTO employees VALUES
 (1, 'John', 'Smith', 1, 100000),
 (2, 'Jane', 'Doe', 1, 95000),
 (3, 'Bob', 'Johnson', 2, 80000),
 (4, 'Samantha', 'Jones', 2, 85000),
 (5, 'Mike', 'Williams', 3, 120000),
 (6, 'Emily', 'Brown', 3, 110000),
 (7, 'David', 'Lee', 4, 75000),
 (8, 'Sarah', 'Miller', 4, 70000);

Query:

SELECT department_id, first_name, last_name, salary
FROM (
 SELECT department_id, first_name, last_name, salary,
 RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
 FROM employees
) AS ranked_employees
WHERE salary_rank <= 3;

Explanation:

This query uses a window function to rank employees within each department by their salary, and then selects only the top 3 employees for each department. This is achieved by using the RANK() function with a PARTITION BY clause, which will reset the ranking for each department. The outer query then filters out any employees with a salary rank higher than 3. This solution allows for ties in salary to be included in the top 3.