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.
employees has name, department and salary. Return the employees whose salary is strictly greater than the average salary of their department, with these columns: name, department, salary dept_avg: the average salary of the department second_highest: the second-highest distinct salary in the department (NULL if the department has only one distinct salary) Assign the DataFrame to result.
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
Interview-style drill: List employees paid above their department's average, with each department's second-highest distinct salary.
`employees` has `name`, `department` and `salary`. Return the employees whose `salary` is strictly greater than the average salary of their department, with these columns: - `name`, `department`, `salary` - `dept_avg`: the average salary of the department - `second_highest`: the second-highest **distinct** salary in the department (NULL if the department has only one distinct salary) Assign the DataFrame to `result`.