Query to Fetch Top 3 Salaried Employees in Each Department

This explanation will cover:

1. The problem statement
2. Solution using window functions
3. Dummy data setup
4. Complete SQL query
5. Expected output

Problem Statement

We need to retrieve the top 3 highest-paid employees from each department in an organization. This requires:

• Grouping employees by department
• Ordering employees within each department by salary (descending)
• Selecting only the top 3 from each group

Solution Approach

The best way to solve this is using window functions, specifically DENSE_RANK(), ROW_NUMBER(), or RANK(). We'll use DENSE_RANK() as it:

• Handles ties properly (employees with same salary get same rank)
• Doesn't skip numbers after ties

Dummy Data Setup

Let's create a sample table with employee data:

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100),
    department VARCHAR(50),
    salary DECIMAL(10,2)
);

INSERT INTO employees VALUES
(1, 'John Smith', 'IT', 85000.00),
(2, 'Jane Doe', 'HR', 72000.00),
(3, 'Mike Johnson', 'IT', 92000.00),
(4, 'Sarah Williams', 'HR', 68000.00),
(5, 'David Brown', 'Finance', 105000.00),
(6, 'Lisa Davis', 'IT', 88000.00),
(7, 'Robert Wilson', 'Finance', 115000.00),
(8, 'Emily Taylor', 'HR', 75000.00),
(9, 'James Anderson', 'Finance', 95000.00),
(10, 'Jessica Martinez', 'IT', 92000.00),
(11, 'Thomas Jackson', 'Finance', 105000.00),
(12, 'Karen White', 'HR', 69000.00),
(13, 'Daniel Harris', 'IT', 81000.00),
(14, 'Michelle Clark', 'Marketing', 78000.00),
(15, 'Christopher Lewis', 'Marketing', 82000.00),
(16, 'Amanda Young', 'Marketing', 76000.00);

Complete SQL Query

WITH ranked_employees AS (
    SELECT 
        emp_id,
        emp_name,
        department,
        salary,
        DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
    FROM 
        employees
)
SELECT 
    emp_id,
    emp_name,
    department,
    salary,
    salary_rank
FROM 
    ranked_employees
WHERE 
    salary_rank <= 3
ORDER BY 
    department, 
    salary_rank;

Expected Output

emp_id | emp_name           | department | salary   | salary_rank
-------+--------------------+------------+----------+------------
5      | David Brown        | Finance    | 105000.00| 1
7      | Robert Wilson      | Finance    | 115000.00| 1
9      | James Anderson     | Finance    | 95000.00 | 2
11     | Thomas Jackson     | Finance    | 105000.00| 1
2      | Jane Doe           | HR         | 72000.00 | 1
8      | Emily Taylor       | HR         | 75000.00 | 2
4      | Sarah Williams     | HR         | 68000.00 | 3
12     | Karen White        | HR         | 69000.00 | 3
3      | Mike Johnson       | IT         | 92000.00 | 1
10     | Jessica Martinez   | IT         | 92000.00 | 1
6      | Lisa Davis         | IT         | 88000.00 | 2
1      | John Smith         | IT         | 85000.00 | 3
15     | Christopher Lewis  | Marketing  | 82000.00 | 1
14     | Michelle Clark     | Marketing  | 78000.00 | 2
16     | Amanda Young       | Marketing  | 76000.00 | 3

Explanation of the Output

1. For the Finance department:

   • Robert Wilson, David Brown, and Thomas Jackson are tied for rank 1 (all have salaries ≥105k)
   • James Anderson is rank 2 (95k)

2. For the HR department:

   • Emily Taylor is rank 1 (75k)
   • Jane Doe is rank 2 (72k)
   • Sarah Williams and Karen White are tied for rank 3 (68k and 69k)

3. For the IT department:

   • Mike Johnson and Jessica Martinez are tied for rank 1 (92k)
   • Lisa Davis is rank 2 (88k)
   • John Smith is rank 3 (85k)

4. For the Marketing department:

   • Christopher Lewis is rank 1 (82k)
   • Michelle Clark is rank 2 (78k)
   • Amanda Young is rank 3 (76k)

Note that in departments with ties, you might get more than 3 rows if multiple employees share the same salary at the rank 3 position. If you strictly want exactly 3 employees per department regardless of ties, you would use ROW_NUMBER() instead of DENSE_RANK().