Salary Share of Department
For each employee, compute their salary as a percentage of their department's total payroll: 100.0 * salary / dept_total, rounded with ROUND(x, 2). Every employee belongs to a department and has a salary. Names are unique.
Return columns dept, name, salary, pct_of_dept. Order by dept ascending, pct_of_dept descending, then name ascending.
Schema
CREATE TABLE employees ( emp_id INTEGER PRIMARY KEY, name TEXT NOT NULL, dept TEXT NOT NULL, salary INTEGER NOT NULL );
Sample data
employees
| emp_id | name | dept | salary |
|---|---|---|---|
| 1 | Aria | Design | 9000 |
| 2 | Bilal | Design | 6000 |
| 3 | Cleo | Engineering | 15000 |
| 4 | Dev | Engineering | 12000 |
| 5 | Eun | Engineering | 13000 |
| 6 | Faye | Support | 5000 |
Hint 1
SUM(salary) OVER (PARTITION BY dept) puts the department total on every row without collapsing rows.
Discussion 0
Join free to post in the discussion.
Good practice for: Data Analyst, Business Analyst, Data Scientist
SQL (SQLite)
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.