Salary percent of department total

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

A manager wants to see how much each person contributes to their department's pay. For each employee, work out their salary as a percent of the total salary of their department. For employees who have a department and a salary, return name, department, salary and pct_of_dept_total, which is ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 2). Sort by department, then by pct_of_dept_total from the highest, then by name.

Requirements

• Return 1 row for each employee who has a department and a salary.
• pct_of_dept_total is the salary divided by the department's total salary, times 100, rounded to 2 decimals.
• The total is calculated per department with a window function.
• Sort by department, pct_of_dept_total descending, then name.

Constraints

• The table is employees.
• Use SUM(salary) OVER (PARTITION BY department) for the department total.
• Multiply by 100.0 (not 100), so the division gives decimals.
• Return only these 4 columns, in this order: name, department, salary, pct_of_dept_total.

Examples

Input: employees (Engineering) name | department | salary Alice Chen | Engineering | 120000 Dan Park | Engineering | 110000 Bob Kumar | Engineering | 95000 Eve Ng | Engineering | 95000 Output: name | department | salary | pct_of_dept_total Alice Chen | Engineering | 120000 | 28.57 Dan Park | Engineering | 110000 | 26.19 Bob Kumar | Engineering | 95000 | 22.62 Eve Ng | Engineering | 95000 | 22.62 Why this passes: Each salary is divided by the Engineering window total (420000), then rounded to 2 decimals.

Topics: lakebench, sql, ratio, window.

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