Department Top Three Salaries | Leetcode 185 | SQL Interview Series

Welcome to the final subquery challenge in our 50-question SQL interview series. In this comprehensive study note, we explore LeetCode 185: Department Top Three Salaries, a classic database problem that evaluates your mastery of multi-table joins, correlated subqueries, and conditional aggregation logic.

1. Problem Statement & Schema Architecture

Before writing a single line of SQL, we must understand the underlying data schemas. The database contains two primary tables:

1. Employee Table: Holds individual worker records.

   • id (Integer, Primary Key): Unique identifier for each employee.
   • name (Varchar): Name of the employee.
   • salary (Integer): Compensation amount.
   • department_id (Integer, Foreign Key): Links the employee to their respective department.

2. Department Table: Holds organizational unit records.

   • id (Integer, Primary Key): Unique identifier for each department.
   • name (Varchar): Name of the department (e.g., IT, Sales).

The Objective

Company executives want to identify the highest earners in each department. Specifically, a "high earner" in a department is defined as an employee whose salary falls within the top three unique salaries for that specific department.

If multiple employees share the exact same salary, they occupy the same salary rank tier. We must return the results with three specific columns: Department, Employee, and Salary, in any order.

2. Status Quo: Why This Query is Tricky

A naive approach might attempt to use GROUP BY or basic aggregate functions (MAX()). However, GROUP BY collapses rows into single summaries per group, making it impossible to retrieve individual employee details alongside a ranking threshold unless handled via window functions (like DENSE_RANK()) or a correlated subquery approach.

In this tutorial, we solve the problem using a Correlated Subquery with a Distinct Count check, which is a powerful, highly portable technique across different SQL dialects.

3. Step 1: Combining Tables with Inner Join

Because employee names and department names reside in two separate tables, we must bridge them using an INNER JOIN on the department identifier.

SELECT 
    d.name AS Department,
    e1.name AS Employee,
    e1.salary AS Salary
FROM 
    Employee e1
INNER JOIN 
    Department d 
ON 
    e1.department_id = d.id;

Visualizing the Join Execution Flow

4. Step 2: The Core Mental Model for Ranking via Subquery

To determine if an employee's salary is within the top three unique salaries of their department, we need to ask a specific question for every single row:

"How many distinct colleagues in the same department earn strictly more money than this employee?"

• If the count of such colleagues is less than 3 ($< 3$), then the employee belongs to the top three tiers.
• If the count is 3 or greater, the employee falls outside the top three.

Real-Life Analogy: The Corporate Ladder

Imagine standing on a company podium where only the top 3 salary tiers are awarded medals. To know if you qualify, you look across your department and count how many people make more money than you do.

• If only 0, 1, or 2 people out-earn you, you step onto the podium.
• If 3 or more people make more than you, you miss the cutoff.

5. Step 3: Writing the Complete Production Query

Here is the complete, working SQL solution combining the join, column selection, and the correlated subquery filter:

SELECT 
    d.name AS Department,
    e1.name AS Employee,
    e1.salary AS Salary
FROM 
    Employee e1
INNER JOIN 
    Department d 
ON 
    e1.department_id = d.id
WHERE 
    (
        SELECT COUNT(DISTINCT e2.salary)
        FROM Employee e2
        WHERE e2.department_id = e1.department_id
          AND e2.salary > e1.salary
    ) < 3;

6. Step-by-Step Breakdown of the Correlated Subquery

Let's dissect the exact mechanics of the WHERE clause condition:

1. Table Aliasing (e1 vs e2):
   • e1 represents the current employee being evaluated in the outer query.
   • e2 represents the comparison employee table scanned inside the inner subquery.
2. Same Department Constraint:
   • e2.department_id = e1.department_id ensures we are only comparing employees within the exact same department.
3. Higher Salary Check:
   • e2.salary > e1.salary filters for colleagues who earn strictly more than employee e1.
4. Distinct Count:
   • COUNT(DISTINCT e2.salary) ensures that tied salaries are grouped into the same rank tier rather than inflating the count.
5. The Threshold Filter:
   • < 3 guarantees that fewer than 3 people earn more than e1, successfully isolating the top 3 salary tiers.

7. Comparative Analysis of Approaches

8. Summary and Key Takeaways

• Multi-Table Mapping: Always establish clear table aliases (e1, e2, d) when performing self-joins and cross-table references.
• Correlated Subquery Execution: A correlated subquery executes once for every row evaluated by the outer query, making it an effective filtering mechanism for relative ranking problems.
• Handling Ties with DISTINCT: Using COUNT(DISTINCT e2.salary) prevents duplicate salary values from throwing off your ranking threshold.
• Threshold Logic (< 3): Translates directly to "Top 3 Unique Salaries" by filtering out employees who have 3 or more people earning more than them.
• Modular Pipeline: Breaking queries down into FROM/JOIN $\rightarrow$ SELECT $\rightarrow$ WHERE filtering ensures clean, bug-free SQL development during high-stakes technical interviews.