Above department average and second-highest salary

PySpark data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 18 minutes. Part of the Pro drill bank.

List employees paid above their department's average, with each department's second-highest distinct salary. Treat this as a production helper: match the contracted return shape, including empty and duplicate inputs.

You have a table employees with name, department and salary. Find the employees who earn more than the average of their own department, and show some department context next to each. Return the employees whose salary is strictly greater than the average salary of their department. The columns are: name, department, salary, dept_avg (the department average, rounded to 2 decimals) and second_highest, the second-highest distinct salary in the department. If the department has only one distinct salary, second_highest is NULL. The editor creates the table employees and ends with result = employees. Change the last line to build your answer.

Requirements

• Calculate the average salary of each department with a window or a group by.
• Keep only employees with salary strictly greater than the department average.
• Add second_highest: the second-highest distinct salary of the department, or NULL.
• Return name, department, salary, dept_avg, second_highest in result.

Constraints

• Use dense_rank() for the second-highest distinct salary, so tied salaries share a rank.
• Round dept_avg to 2 decimals.
• Use a left join (or a window) so departments with a single salary still return NULL for second_highest.

Examples

Input: employees name | department | salary Ana | Eng | 120 Ben | Eng | 100 Cy | Eng | 100 Dee | Eng | 80 Eli | Ops | 70 Fay | Ops | 90 Gus | Ops | 90 Hal | Hr | 60 Ian | Hr | 65 Joy | Legal | 55 Output: name | department | salary | dept_avg | second_highest Ana | Eng | 120 | 100 | 100 Fay | Ops | 90 | 83.33 | 70 Gus | Ops | 90 | 83.33 | 70 Ian | Hr | 65 | 62.5 | 60 Ana beats Eng's average of 100. Fay and Gus (90) beat Ops's 83.3, whose second distinct salary is 70. Ian beats Hr's 62.5. Nobody in Legal is above their own average.

Topics: lakebench, pyspark, window, avg over, dense_rank.

More PySpark interview questions · All interview problems · Learn data engineering