SQL data engineering interview problem. Difficulty: intermediate. Pattern: Window Functions. About 12 minutes. Part of the Pro drill bank.
For employees with non-null department and salary, return: name, department, salary, Order by department, salary, name. Return columns in this order: name, department, salary, percentile_rank.
Input: employees (Engineering) name | department | salary Bob Kumar | Engineering | 95000 Eve Ng | Engineering | 95000 Dan Park | Engineering | 110000 Alice Chen | Engineering | 120000 Output: name | department | salary | percentile_rank Bob Kumar | Engineering | 95000 | 0.0 Eve Ng | Engineering | 95000 | 0.0 Dan Park | Engineering | 110000 | 0.67 Alice Chen | Engineering | 120000 | 1.0 Why this passes: PERCENT_RANK is (rank-1)/(n-1) within the department partition. Tied lowest salaries both get 0.0.
Topics: lakebench, sql, percent_rank.
More SQL interview questions · All interview problems · Learn data engineering
Interview-style drill: PERCENT_RANK of salary within department.
For employees with non-null department and salary, return: `name`, `department`, `salary`, `percentile_rank` = ROUND(PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary), 2) Order by `department`, `salary`, `name`.