Shares, Comparisons and Bands

Partition totals next to each row

An aggregate over a partition without ORDER BY gives the group total on every row, so salary / sum(salary) OVER (PARTITION BY dept) is each person's share. first_value compares each row with the top of its group, and NTILE(n) splits ordered rows into n roughly equal bands (quartiles, deciles). Named windows (WINDOW w AS (...)) avoid repeating long definitions.

Share of department pay, gap to the top and pay bands, run

I ran this with psql on PostgreSQL 16.2 against the course sample data (shown in the first section). Percentages are within each department; vs_top is the difference from the highest salary in the department; NTILE(3) splits 8 people into bands of 3, 3 and 2.

SELECT name, dept_id, salary,
       round(100.0 * salary / sum(salary) OVER (PARTITION BY dept_id), 1) AS pct_of_dept,
       salary - first_value(salary) OVER (PARTITION BY dept_id ORDER BY salary DESC) AS vs_top,
       NTILE(3) OVER (ORDER BY salary DESC) AS pay_band
FROM employees WHERE dept_id IS NOT NULL
ORDER BY dept_id, salary DESC, name;

Output:

 name  | dept_id | salary | pct_of_dept | vs_top  | pay_band 
-------+---------+--------+-------------+---------+----------
 Asha  |       1 | 250000 |        34.2 |       0 |        1
 Meera |       1 | 180000 |        24.7 |  -70000 |        1
 Ravi  |       1 | 180000 |        24.7 |  -70000 |        1
 John  |       1 | 120000 |        16.4 | -130000 |        2
 Sara  |       2 | 150000 |        44.8 |       0 |        2
 Li    |       2 |  95000 |        28.4 |  -55000 |        2
 Ken   |       2 |  90000 |        26.9 |  -60000 |        3
 Omar  |       3 |  70000 |       100.0 |       0 |        3
(8 rows)

Multiply by 100.0, not 100

Integer division truncates; using a numeric literal keeps decimals.

त्वरित जाँच: What does sum(salary) OVER (PARTITION BY dept_id) return on each row?

• A running total
• The total salary of that row's department
• The overall total
• The row's own salary