Write a query to find the top three highest-paid employees in each department using window functions.

To find the top three highest-paid employees in each department, you use a window function to rank employees within their respective departments, then filter for those ranks. The choice between DENSE_RANK() and ROW_NUMBER() depends on how ties in salary should be handled.

Mechanics and Why

The core of this solution involves a window function, specifically DENSE_RANK() or ROW_NUMBER(), applied over partitions of data.

PARTITION BY department: This clause divides the employees into separate groups, one for each department. The ranking will then be performed independently within each of these groups.

ORDER BY salary DESC: Within each department partition, employees are sorted by their salary in descending order, so the highest earners receive the lowest rank numbers.

DENSE_RANK() vs. ROW_NUMBER():

* DENSE_RANK(): Assigns a rank to each row within its partition, with no gaps in the ranking sequence if there are ties. If two employees in a department have the same highest salary, they both get rank 1, and the next highest salary gets rank 2. This means you might get more than three employees if there are ties for the 3rd position. * ROW_NUMBER(): Assigns a unique, sequential integer rank to each row within its partition. If two employees have the same salary, their rank is determined by their order within the partition, which can be arbitrary unless an additional tie-breaker (e.g., employee_id) is added to the ORDER BY clause. This guarantees exactly three employees per department (unless a department has fewer than three).

Common Table Expression (CTE): Using a WITH clause (CTE) named ranked improves readability by first calculating the ranks and then performing the final selection and filtering.

Filtering: The outer SELECT statement then filters the results where the calculated rank (rn) is less than or equal to 3.

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

Business Nuance and Trade-offs

The choice between DENSE_RANK() and ROW_NUMBER() is a critical business decision. If two employees tie for the third highest salary, DENSE_RANK() will include both, potentially returning four or more employees for that department. ROW_NUMBER(), conversely, will strictly return three employees per department, requiring a deterministic tie-breaker (e.g., ORDER BY salary DESC, employee_id ASC) to ensure consistent results.

For very large datasets, PARTITION BY operations can be computationally expensive in distributed systems like Spark, as they often necessitate a full data shuffle across nodes. Data engineers should be aware of these performance implications and consider optimizing the underlying table structure (e.g., using clustering keys in Snowflake or partitioning in Delta Lake) if this query is run frequently on massive tables.

In the interview, also mention the importance of clarifying the business requirement for tie-breaking and discussing the performance implications of window functions on large-scale data.