Write a SQL query to find top 3 earners in each department.

To find the top 3 earners in each department, use a SQL window function with ROW_NUMBER() or DENSE_RANK() partitioned by department, then filter the results. ROW_NUMBER() ensures exactly three employees per department, while DENSE_RANK() includes all employees tied for the 3rd position, potentially returning more than three.

Mechanics and Why

The core of this solution leverages SQL window functions, specifically ROW_NUMBER() or DENSE_RANK(), combined with PARTITION BY and ORDER BY.
* PARTITION BY department: This clause divides the dataset into independent groups, with one partition for each unique department. The ranking function then operates independently within these groups.
* ORDER BY salary DESC: Within each department partition, employees are sorted by their salary in descending order, ensuring that the highest earners receive the lowest ranks.
* ROW_NUMBER(): Assigns a unique, sequential integer rank to each row within its partition. If multiple employees have the same salary, they will receive distinct ranks (e.g., 1, 2, 3) based on their physical order or additional tie-breaking columns in the ORDER BY clause. This guarantees exactly N rows per partition.
* DENSE_RANK(): Assigns ranks without gaps, and employees with identical values in the ORDER BY clause receive the same rank. For instance, if two employees tie for the 2nd highest salary, both would receive rank 2, and the next distinct salary would receive rank 3. This is preferred when business logic dictates including all tied earners.

A Common Table Expression (CTE), like ranked_employees, enhances readability by clearly separating the ranking logic from the final selection and filtering step.

WITH ranked_employees AS (
    SELECT
        employee_id,
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM
        employees
)
SELECT
    employee_id,
    name,
    department,
    salary
FROM
    ranked_employees
WHERE
    rn <= 3;

Key Trade-offs and Scalability

The primary trade-off lies in choosing between ROW_NUMBER() and DENSE_RANK(), which depends entirely on the business requirement for handling ties at the Nth position.

From a scalability perspective, PARTITION BY operations can be resource-intensive, especially on large datasets. In distributed systems like Apache Spark or cloud data warehouses such as Snowflake, partitioning typically involves a "shuffle" operation where data is redistributed across nodes based on the partitioning key (department). This data movement can be a significant performance bottleneck. The ORDER BY clause within each partition also necessitates an in-memory or disk-based sort.

To optimize performance, ensure the underlying table is structured efficiently. In traditional relational databases, an index on (department, salary DESC) can dramatically speed up both the partitioning and sorting steps. In columnar data warehouses like Snowflake, defining department as a clustering key can co-locate related data within micro-partitions, reducing scan times and improving sort efficiency.

In the interview, also mention the importance of clarifying the tie-breaking rule (i.e., ROW_NUMBER vs. DENSE_RANK) with the interviewer, as it demonstrates an understanding of business requirements and edge cases.