Tag archive
Commands grouped around the same concept, pattern, or recurring problem.
Use `NTILE(n)` to divide an ordered result set into `n` roughly equal buckets and assign each row a bucket number.
Use COUNT as a window function to number how many rows have appeared up to the current row.
Use percent-rank window functions to express each row's position within an ordered group.
Find the median or any percentile value using `PERCENTILE_CONT` in PostgreSQL, MariaDB, and SQL Server, or a window-function workaround in MySQL and SQLite.
Use `ROWS BETWEEN` in a window function to aggregate only the rows immediately surrounding each row.
Use LAST_VALUE with an explicit full-partition frame to compare each row with the final row in its group.
Define a window specification once with a `WINDOW` alias and reference it in multiple `OVER` clauses — avoiding repetition when several window functions share the same partition and ordering.
Use PostgreSQL's `DISTINCT ON (col)` to keep only the first row for each unique value of a column, with `ORDER BY` controlling which row within each group is considered first.