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