# SQL Window Functions Cheat Sheet
_Ranking, running totals and period-over-period without a self-join_
Window functions compute across rows while keeping every row. They replace most self-joins and correlated subqueries, usually faster and always clearer.
> Difficulty: intermediate  
> Version: 1.0  
> Updated: 2025-08-19  
> Categories: SQL  
> Tags: Regex

Source: https://invitationbuddy.com/cheat-sheet/sql-window-functions-cheat-sheet

---

## Functions
| Function | Returns |
| --- | --- |
| `ROW_NUMBER()` | A unique sequential number; ties broken arbitrarily |
| `RANK()` | Ties share a rank, then the next value SKIPS (1,1,3) |
| `DENSE_RANK()` | Ties share a rank, no gaps (1,1,2) |
| `NTILE(4)` | Buckets the partition into quartiles |
| `LAG(x, 1), LEAD(x, 1)` | The previous and next row's value |
| `FIRST_VALUE(x), LAST_VALUE(x)` | Edge values — LAST_VALUE needs an explicit frame |
| `SUM(x) OVER (ORDER BY d)` | Running total |

## Anatomy
| Clause | Controls |
| --- | --- |
| `PARTITION BY` | Restarts the calculation per group |
| `ORDER BY` | Row order inside the partition |
| `ROWS BETWEEN ... AND ...` | A frame counted in ROWS |
| `RANGE BETWEEN ... AND ...` | A frame counted in VALUES of the ORDER BY column |
| `ROWS UNBOUNDED PRECEDING AND CURRENT ROW` | Classic running total |
| `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW` | Trailing 7-row moving average |

## ROWS is not RANGE
With an ORDER BY and no frame clause, the default is RANGE UNBOUNDED PRECEDING — which includes every PEER row sharing the current ORDER BY value. On a date column with several rows per day, a "running total" written that way jumps by the whole day at once. Write ROWS explicitly whenever ties are possible.

## Code examples
### Running total and period-over-period
One pass, no self-join. Note the explicit ROWS frame.
```sql
SELECT
    order_date,
    region,
    revenue,

    SUM(revenue) OVER (
        PARTITION BY region
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total,

    revenue - LAG(revenue) OVER (
        PARTITION BY region ORDER BY order_date
    ) AS vs_previous,

    DENSE_RANK() OVER (
        PARTITION BY region ORDER BY revenue DESC
    ) AS rank_in_region

FROM orders
ORDER BY region, order_date;
```

## FAQs
**Why is my running total jumping?**
You omitted the frame clause, so it defaulted to RANGE, which includes every peer row sharing the current ORDER BY value. With several rows per day the total advances a whole day at a time. Write ROWS explicitly.

**RANK or DENSE_RANK?**
RANK leaves gaps after a tie (1, 1, 3); DENSE_RANK does not (1, 1, 2). Use RANK for competition placing and DENSE_RANK when you need contiguous levels.

---
_Generated from https://invitationbuddy.com/cheat-sheet/sql-window-functions-cheat-sheet_