Salary percentile within department

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

You want to know where each employee stands inside their department's salary range: at the bottom, in the middle, or at the top. A percentile rank gives this as a number from 0 (the lowest) to 1 (the highest). For employees who have a department and a salary, return name, department, salary and percentile_rank, which is ROUND(PERCENT_RANK() OVER (PARTITION BY department ORDER BY salary), 2). Sort by department, salary and name.

Requirements

• Calculate PERCENT_RANK() separately for each department.
• Order the salaries from lowest to highest inside the window.
• Round the result to 2 decimal places.
• Ignore rows with a NULL department or NULL salary.
• Sort by department, salary ascending, then name.

Constraints

• The table is employees.
• PERCENT_RANK is (rank - 1) / (number of rows in the group - 1).
• Return only these 4 columns, in this order: name, department, salary, percentile_rank.

Examples

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