Lifted from: Timmy Kurian’s Linkedin

Problem:

Given a table of employees with their salaries, find the top 5 highest paid employees in each department.

Sample Tables (CREATE + INSERT):

CREATE TABLE employees (
 id INT,
 name VARCHAR(50),
 department VARCHAR(50),
 salary INT
);
INSERT INTO employees (id, name, department, salary)
VALUES (1, 'John', 'Sales', 80000),
 (2, 'Jane', 'Marketing', 90000),
 (3, 'Bob', 'Sales', 75000),
 (4, 'Alice', 'Marketing', 85000),
 (5, 'Mike', 'Finance', 100000),
 (6, 'Sara', 'Finance', 95000),
 (7, 'Tom', 'HR', 70000),
 (8, 'Emily', 'HR', 60000),
 (9, 'Mark', 'IT', 120000),
 (10, 'Lisa', 'IT', 110000);

Query:

SELECT id, name, department, salary
FROM (
 SELECT *,
 DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rank
 FROM employees
) AS ranked_employees
WHERE salary_rank <= 5;

Explanation:

This query uses the DENSE_RANK() window function to rank employees within their respective departments based on their salary. It then filters for only the top 5 highest paid employees in each department. This can be useful for identifying top performers or potential discrepancies in salary distribution within a company.