SQL Window Functions with Examples
PostgreSQL window functions and explicit frame patterns for ranks, running totals, and neighboring rows.
PostgreSQL window functions and explicit frame patterns for ranks, running totals, and neighboring rows. Each row includes a practical application and a failure to check before using it.
Use it when
- Calculate rankings without collapsing detail rows.
- Build a running total or compare adjacent events.
Watch out for
- Examples use PostgreSQL syntax.
- An ORDER BY inside OVER does not guarantee final output ordering.
The data
12 rows
| Function | Example | Use | Pitfall |
|---|---|---|---|
| ROW_NUMBER | ROW_NUMBER() OVER (PARTITION BY team ORDER BY score DESC, id) | Number rows within each group. | Add a stable tie-breaker when order matters. |
| RANK | RANK() OVER (ORDER BY score DESC) | Give tied rows the same rank with gaps. | It differs from dense rank after a tie. |
| DENSE_RANK | DENSE_RANK() OVER (ORDER BY score DESC) | Rank distinct score groups without gaps. | It does not choose one winner from tied rows. |
| LAG | LAG(amount) OVER (ORDER BY day) | Read the preceding row. | The previous row is not necessarily the previous calendar day. |
| LEAD | LEAD(amount) OVER (ORDER BY day) | Read the following row. | Missing neighbors yield the specified default or null. |
| FIRST_VALUE | FIRST_VALUE(price) OVER (PARTITION BY item ORDER BY day) | Read the first value in the frame. | Frame semantics affect the result. |
| LAST_VALUE | LAST_VALUE(price) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) | Read the last value over the whole partition. | The default frame often ends at the current peer group. |
| SUM running total | SUM(amount) OVER (ORDER BY day, id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) | Compute a deterministic row-by-row cumulative sum. | The default RANGE frame groups peer rows. |
| AVG moving rows | AVG(amount) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) | Average the current and previous six rows. | Seven rows do not imply seven calendar days. |
| COUNT group size | COUNT(*) OVER (PARTITION BY team) | Keep detail rows while attaching a group total. | Joining first can duplicate rows and inflate the count. |
| NTILE | NTILE(4) OVER (ORDER BY score) | Split ordered rows into roughly equal buckets. | Tied values can land in different buckets. |
| PERCENT_RANK | PERCENT_RANK() OVER (ORDER BY score) | Express relative rank within a partition. | A one-row partition returns zero. |
Fields
The shape of this dataset, read from the data itself: every column, its type and a real value. Agents can use this to plan a query without fetching the whole document.
| Section | Field | Type | Example |
|---|---|---|---|
| data | function | string | ROW_NUMBER |
| data | example | string | ROW_NUMBER() OVER (PARTITION BY team ORDER BY s… |
| data | use | string | Number rows within each group. |
| data | pitfall | string | Add a stable tie-breaker when order matters. |
Fetch the same bytes
The static files contain the same reference rows as the API, with no API rate limit. Live metadata, such as view counts, may differ. Use the API for content negotiation; use the static files for a cacheable snapshot.
curl "https://yjtoon.com/api/dataset/sql-window-functions?format=toon"
const res = await fetch( "https://yjtoon.com/static-data/dataset/sql-window-functions.toon" ); const toon = await res.text();
Rate limit: 120 requests per minute per IP, no key and no signup. API reference →
Sources and editorial notes
Original editorial reference notes by YJTOON, informed by the linked primary documentation. External specifications retain their own licenses.
Editorial review: . Source documents keep their own licenses.