Bristol Myers Squibb Data Engineer Interview Questions (2026)

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. 1. **`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. 2. **`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. 3. **`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). 4. **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. 5. **Filtering**: The outer `SELECT` statement then filters the results where the calculated rank (`rn`) is less than or equal to 3. ```sql 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.