NTILE, FIRST_VALUE & frames

Buckets, percentiles, and pulling a value from across the window.

More of the window toolkit

ntile(n) — split the ordered rows into n roughly equal buckets (quartiles, deciles…)
first_value(expr) / last_value(expr) — the value from the first/last row of the window frame
percent_rank(), cume_dist() — relative standing within the partition

The frame controls which rows a window sees. With an ORDER BY, the default frame runs from the start of the partition to the current row — so first_value(salary) OVER (PARTITION BY dept ORDER BY salary DESC) is the top salary in each department.

ntile(4) OVER (ORDER BY price). Ten rows do not divide by four, so Postgres makes the earliest buckets the larger ones — 3, 3, 2, 2, never a bucket short by more than one.

first_value(salary) OVER (PARTITION BY department ORDER BY salary DESC). Every row in a partition looks back to the same first row, so dept_top is constant within a department and changes only when the partition does.