# Shares, Comparisons and Bands — SQL Deep Dive

Source: https://www.skillbyai.com/en/sql-deep-dive/w-share

> Compare each row to its group.

## 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.

```sql
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.

**Quiz:** What does sum(salary) OVER (PARTITION BY dept_id) return on each row?

- [ ] A running total
- [x] The total salary of that row's department
- [ ] The overall total
- [ ] The row's own salary

*Answer:* The total salary of that row's department. No ORDER BY means the whole partition.