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