The snippet library.
Small, practical examples. Search by task or narrow things down below.
Calculate a cumulative integer percentage
Postgres: Divide a running window sum by the complete total using a unique ordering key.
Postgres✓ Tested
Divide an ordered result into numbered buckets
Postgres: NTILE distributes rows across buckets; include a unique tie-breaker for deterministic assignment.
Postgres✓ Tested
Rank tied scores with competition gaps
Postgres: RANK leaves a gap after tied rows, unlike DENSE_RANK.
Postgres✓ Tested
Rank tied scores without gaps
Postgres: DENSE_RANK assigns equal ranks to ties and increments by one for the next distinct score.
Postgres✓ Tested
Read the last value of the complete partition
Postgres: Specify an unbounded-following frame so LAST_VALUE does not stop at the current row.
Postgres✓ Tested
Read the next row’s value
Postgres: LEAD reads a later ordered row without a self join and supports a fallback at the end.
Postgres✓ Tested
Repeat the first ordered value beside each row
Postgres: FIRST_VALUE reads the first value in the current window frame.
Postgres✓ Tested
Select the top row in each group with a tie-breaker
Postgres: Filter an outer query on ROW_NUMBER because window functions are not evaluated in WHERE.
Postgres✓ Tested
Show a group total beside each detail row
Postgres: A partitioned aggregate preserves detail rows instead of collapsing them with GROUP BY.
Postgres✓ Tested
Sum a row and its immediate predecessor
Postgres: A ROWS frame counts physical ordered rows rather than all peers with the same sort value.
Postgres✓ Tested