Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing

Salary percent of department total

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 pct_of_dept_total: ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 2) Order by department, pct_of_dept_total descending, name.

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

intermediate

Salary percent of department total

Interview-style drill: Each salary as a percentage of its department total.

For employees with non-null department and salary, return: - `name`, `department`, `salary` - `pct_of_dept_total`: ROUND(100.0 * salary / SUM(salary) OVER (PARTITION BY department), 2) Order by `department`, `pct_of_dept_total` descending, `name`.