codejump
HardPro challengeSQL

Each Employee's Share of Department Budget

SQLDatabases

Write a SQL query that shows, for each employee, their name,
department, salary, and **what percentage of their department's
total salary budget they represent** — rounded to 1 decimal place.

Order the output by department, then by salary descending.

Table: employees

idnamedepartmentsalary
1AliceEngineering60000
2BobEngineering40000
3DaveMarketing75000
4EveMarketing50000
5FrankMarketing25000

Engineering total = 100 000 → Alice 60 %, Bob 40 %
Marketing total = 150 000 → Dave 50 %, Eve 33.3 %, Frank 16.7 %

Expected output (columns: name, department, salary, pct)

Alice|Engineering|60000|60.0
Bob|Engineering|40000|40.0
Dave|Marketing|75000|50.0
Eve|Marketing|50000|33.3
Frank|Marketing|25000|16.7

Combine SUM(salary) OVER (PARTITION BY department) with regular

arithmetic — this is the pattern that separates intermediate from

advanced SQL practitioners.

Sample tests

Test #1Engineering (100k total), Marketing (150k total)
Input: "CREATE TABLE employees (id INTEGER, name TEXT, department TEXT, salary INTEGER);\nINSERT INTO employees VALUES\n (1, 'Alice', 'Engineering', 60000),\n (2, 'Bob', 'Engineering', 40000),\n (3, 'Dave', 'Marketing', 75000),\n (4, 'Eve', 'Marketing', 50000),\n (5, 'Frank', 'Marketing', 25000);"
Output: "Alice|Engineering|60000|60.0\nBob|Engineering|40000|40.0\nDave|Marketing|75000|50.0\nEve|Marketing|50000|33.3\nFrank|Marketing|25000|16.7"
← All challenges