Window functions let you run a calculation across a set of rows that are related to the current row - a running total, a rank, a comparison to the previous row - without collapsing those rows into one. They are the single biggest leap in expressive power you can add to your SQL, and once they click you will reach for them constantly.
The trick that confuses most people at first is simple: a plain aggregate like SUM(amount) with GROUP BY returns one row per group, but a window function like SUM(amount) OVER (...) returns one row per input row, with the aggregate glued onto each of them. Same math, completely different shape of output. This guide walks through every family of window function with runnable SQL and the exact rows each query returns, then finishes with practical recipes, performance notes, and database-support details for MySQL 8+, PostgreSQL, and SQL Server.
Throughout we use one small sales table so you can follow every result by eye. Here it is:
-- Our running example: 8 rows of sales
CREATE TABLE sales (
id INT,
region VARCHAR(10),
rep VARCHAR(10),
sale_day DATE,
amount INT
);| id | region | rep | sale_day | amount |
|---|---|---|---|---|
| 1 | East | Ana | 2026-01-01 | 100 |
| 2 | East | Ana | 2026-01-02 | 200 |
| 3 | East | Ben | 2026-01-03 | 200 |
| 4 | East | Ben | 2026-01-04 | 150 |
| 5 | West | Cy | 2026-01-01 | 300 |
| 6 | West | Cy | 2026-01-02 | 300 |
| 7 | West | Di | 2026-01-03 | 250 |
| 8 | West | Di | 2026-01-04 | 400 |
What this guide covers
- Window function vs GROUP BY
- The OVER() clause: PARTITION BY and ORDER BY
- Window frames: ROWS vs RANGE
- Ranking: ROW_NUMBER, RANK, DENSE_RANK, NTILE
- Offset functions: LAG, LEAD, FIRST/LAST/NTH_VALUE
- Aggregate windows: running totals and moving averages
- Practical recipes with results
- Performance and gotchas
- Database support notes
1. What a window function is (and how it differs from GROUP BY)
A window function performs a calculation across a set of rows - the "window" - that is somehow related to the current row. The defining feature is that it keeps every row. Nothing is collapsed. Compare the two queries below side by side: they compute the same regional total, but one throws your detail rows away and the other keeps them.
-- Collapses to one row per region
SELECT region, SUM(amount) AS region_total
FROM sales
GROUP BY region;| region | region_total |
|---|---|
| East | 650 |
| West | 1250 |
-- Keeps all 8 rows, adds the region total to each
SELECT id, region, amount,
SUM(amount) OVER (PARTITION BY region) AS region_total
FROM sales
ORDER BY id;| id | region | amount | region_total |
|---|---|---|---|
| 1 | East | 100 | 650 |
| 2 | East | 200 | 650 |
| 3 | East | 200 | 650 |
| 4 | East | 150 | 650 |
| 5 | West | 300 | 1250 |
| 6 | West | 300 | 1250 |
| 7 | West | 250 | 1250 |
| 8 | West | 400 | 1250 |
This is why window functions are so useful: you can show a row and its context at the same time - the individual sale next to its regional total, so you can compute "this sale is what percent of its region?" in a single query. With GROUP BY you would need a join back to the aggregated result. If you want a broader tour of aggregate helpers first, the SQL functions list and the SQL functions reference are good companions to this page.
It helps to name the three moving parts precisely, because the rest of this guide leans on them:
- Partition - the group of rows the function is allowed to look at. Everything is computed independently inside each partition, and the function forgets everything at the boundary.
- Ordering - the sequence rows take within a partition. This is what makes "previous row", "running", and "rank" meaningful. It is separate from the query's outer ORDER BY.
- Frame - the sliding slice of the ordered partition the function actually reads for the current row (all of it, or just a window around the current row).
A plain aggregate ignores ordering and frame and just squashes the group. A window function is that same aggregate, but taught to respect order and position - and to hand the answer back on every row instead of one. Hold that mental model and the syntax stops feeling arbitrary.
Key idea: a window function never removes rows and never needs GROUP BY. It computes over a window and returns the answer on every row that fed into it. You can even mix several window functions with different windows in one SELECT.
2. The OVER() clause: PARTITION BY and ORDER BY
Every window function is followed by OVER (...). That clause defines the window. It has three optional parts, and they run in this logical order:
- PARTITION BY - splits rows into groups (partitions). The function restarts for each partition. Leave it out and the whole result set is one partition.
- ORDER BY - orders the rows within each partition. This matters enormously for running totals, ranking, and offset functions.
- frame (ROWS / RANGE) - narrows the window to a sliding range of rows around the current one. Covered in section 3.
PARTITION BY is conceptually like GROUP BY, but it does not collapse rows. Below, we partition by region and order by day so the running total resets at each region and accumulates in date order:
SELECT region, sale_day, amount,
SUM(amount) OVER (
PARTITION BY region
ORDER BY sale_day
) AS running_total
FROM sales
ORDER BY region, sale_day;| region | sale_day | amount | running_total |
|---|---|---|---|
| East | 2026-01-01 | 100 | 100 |
| East | 2026-01-02 | 200 | 300 |
| East | 2026-01-03 | 200 | 500 |
| East | 2026-01-04 | 150 | 650 |
| West | 2026-01-01 | 300 | 300 |
| West | 2026-01-02 | 300 | 600 |
| West | 2026-01-03 | 250 | 850 |
| West | 2026-01-04 | 400 | 1250 |
Notice the total resets to 300 when the region flips to West - that is the partition boundary. Also notice something subtle and important: adding ORDER BY inside OVER quietly changes the default frame. Without ORDER BY, an aggregate window covers the entire partition (that is why section 1 gave the same 650 on every East row). With ORDER BY, it defaults to "everything from the start of the partition up to the current row," which produces the running total you see here. That default frame is the subject of the next section.
Naming a window with the WINDOW clause
If several functions share the same window, define it once with a named WINDOW clause (supported in MySQL 8+, PostgreSQL, and SQL Server 2022+ / standard):
SELECT region, amount,
SUM(amount) OVER w AS running,
AVG(amount) OVER w AS avg_so_far
FROM sales
WINDOW w AS (PARTITION BY region ORDER BY sale_day)
ORDER BY region, sale_day;3. Window frames: ROWS vs RANGE
The frame is the sliding sub-window of rows, relative to the current row, that an aggregate actually sees. You write it after ORDER BY using either ROWS (count physical rows) or RANGE (rows with values in a logical range). The frame is bounded by two endpoints. Here are the pieces you can use:
| Frame spec | Meaning |
|---|---|
| UNBOUNDED PRECEDING | Start at the first row of the partition |
| n PRECEDING | n rows before the current row |
| CURRENT ROW | The current row (for RANGE, its peers too) |
| n FOLLOWING | n rows after the current row |
| UNBOUNDED FOLLOWING | End at the last row of the partition |
Two frames you will use constantly:
-- Running total (start of partition -> current row)
SUM(amount) OVER (
ORDER BY sale_day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)
-- 3-row moving window (previous 2 + current)
AVG(amount) OVER (
ORDER BY sale_day
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
)The critical difference between ROWS and RANGE
ROWS counts physical rows. RANGE groups rows that are tied on the ORDER BY value and treats them as one unit (peers). This bites when your ordering column has duplicates. Watch what happens ordering by amount where two East rows share amount 200:
SELECT id, amount,
SUM(amount) OVER (ORDER BY amount
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_rows,
SUM(amount) OVER (ORDER BY amount
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS by_range
FROM sales WHERE region = 'East'
ORDER BY amount;| id | amount | by_rows | by_range |
|---|---|---|---|
| 1 | 100 | 100 | 100 |
| 4 | 150 | 250 | 250 |
| 2 | 200 | 450 | 650 |
| 3 | 200 | 650 | 650 |
The two rows with amount 200 are peers under RANGE, so both jump straight to 650 (the frame includes both ties at once). Under ROWS they accumulate one row at a time: 450 then 650. The default frame when you specify ORDER BY without an explicit frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which means running totals on a column with duplicate order values can surprise you. When in doubt, write ROWS explicitly.
Watch out: for running totals, prefer ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. The implicit RANGE default will lump tied rows together and inflate intermediate totals - a classic silent bug.
4. Ranking functions: ROW_NUMBER, RANK, DENSE_RANK, NTILE
Ranking functions assign a position to each row within its partition, based on the ORDER BY inside OVER. They differ only in how they treat ties:
| Function | Ties get… | Gaps after ties? | Typical use |
|---|---|---|---|
| ROW_NUMBER() | different numbers (arbitrary among ties) | n/a - always 1,2,3,4 | dedupe, pick one row per group |
| RANK() | the same rank | Yes (skips numbers) | competition ranking |
| DENSE_RANK() | the same rank | No (no skips) | ranking with contiguous levels |
| NTILE(n) | spread into n buckets as evenly as possible | n/a | quartiles, percentiles, batching |
Run all four at once over the West region, ordered by amount descending, and the difference is obvious:
SELECT rep, amount,
ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn,
RANK() OVER (ORDER BY amount DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY amount DESC) AS dense,
NTILE(2) OVER (ORDER BY amount DESC) AS tile
FROM sales WHERE region = 'West';| rep | amount | rn | rnk | dense | tile |
|---|---|---|---|---|---|
| Di | 400 | 1 | 1 | 1 | 1 |
| Cy | 300 | 2 | 2 | 2 | 1 |
| Cy | 300 | 3 | 2 | 2 | 2 |
| Di | 250 | 4 | 4 | 3 | 2 |
Read the two tied 300s carefully. ROW_NUMBER gives them 2 and 3 (an arbitrary tiebreak). RANK gives both 2, then jumps to 4 - it leaves a gap. DENSE_RANK gives both 2, then continues at 3 - no gap. NTILE(2) splits four rows into two buckets of two. Because ROW_NUMBER ties are arbitrary, always add a deterministic tiebreaker (for example ORDER BY amount DESC, id) when the choice matters.
Which one do you want in practice? Use ROW_NUMBER whenever you need exactly one row per key (dedupe, "latest record", top-1). Use RANK when ties genuinely share a position and skipping is correct - think leaderboards where two golds mean no silver. Use DENSE_RANK when you want compact levels with no holes, such as pricing tiers or "the third-highest distinct salary". Use NTILE when you care about equal-sized buckets rather than exact values.
Distribution: PERCENT_RANK and CUME_DIST
Two more ranking-family functions express position as a fraction between 0 and 1, which is what most "top X percent" questions actually need. PERCENT_RANK is (rank - 1) / (rows - 1); CUME_DIST is the fraction of rows at or below the current value. Over all eight sales ordered by amount:
SELECT amount,
ROUND(PERCENT_RANK() OVER (ORDER BY amount), 2) AS pct_rank,
ROUND(CUME_DIST() OVER (ORDER BY amount), 2) AS cume_dist
FROM sales
ORDER BY amount;| amount | pct_rank | cume_dist |
|---|---|---|
| 100 | 0.00 | 0.13 |
| 150 | 0.14 | 0.25 |
| 200 | 0.29 | 0.50 |
| 200 | 0.29 | 0.50 |
| 250 | 0.57 | 0.63 |
| 300 | 0.71 | 0.88 |
| 300 | 0.71 | 0.88 |
| 400 | 1.00 | 1.00 |
Filter cume_dist >= 0.9 in an outer query and you have the top 10 percent by value - no magic numbers, no hard-coded thresholds.
5. Offset functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE
Offset functions reach to other rows in the window relative to the current one. They are how you compare a row to its neighbour without a self-join.
- LAG(col, n, default) - value from n rows before (default n = 1).
- LEAD(col, n, default) - value from n rows after.
- FIRST_VALUE(col) - first value in the frame.
- LAST_VALUE(col) - last value in the frame (mind the frame!).
- NTH_VALUE(col, n) - the n-th value in the frame.
SELECT sale_day, amount,
LAG(amount) OVER (ORDER BY sale_day) AS prev_day,
LEAD(amount) OVER (ORDER BY sale_day) AS next_day,
amount - LAG(amount) OVER (ORDER BY sale_day) AS day_change
FROM sales WHERE region = 'West'
ORDER BY sale_day;| sale_day | amount | prev_day | next_day | day_change |
|---|---|---|---|---|
| 2026-01-01 | 300 | NULL | 300 | NULL |
| 2026-01-02 | 300 | 300 | 250 | 0 |
| 2026-01-03 | 250 | 300 | 400 | -50 |
| 2026-01-04 | 400 | 250 | NULL | 150 |
The first row has no previous row, so LAG returns NULL; supply a third argument to substitute a value, for example LAG(amount, 1, 0). LEAD is the mirror image at the tail.
The LAST_VALUE frame trap
LAST_VALUE almost never does what beginners expect, because the default frame ends at CURRENT ROW. So "last value" means "last value seen so far," which is just the current row. To get the true last value of the partition, extend the frame to UNBOUNDED FOLLOWING:
-- Returns the current row's amount, not the partition's last
LAST_VALUE(amount) OVER (ORDER BY sale_day)LAST_VALUE(amount) OVER (
ORDER BY sale_day
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)FIRST_VALUE does not have this problem because the frame already starts at UNBOUNDED PRECEDING. A common idiom is to grab the top item per group: FIRST_VALUE(rep) OVER (PARTITION BY region ORDER BY amount DESC).
Put FIRST_VALUE, NTH_VALUE, and a full-partition LAST_VALUE together to attach "best, second-best, worst" context to every row of the West region:
SELECT rep, amount,
FIRST_VALUE(amount) OVER w AS best,
NTH_VALUE(amount, 2) OVER w AS second,
LAST_VALUE(amount) OVER w AS worst
FROM sales WHERE region = 'West'
WINDOW w AS (ORDER BY amount DESC
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
ORDER BY amount DESC;| rep | amount | best | second | worst |
|---|---|---|---|---|
| Di | 400 | 400 | 300 | 250 |
| Cy | 300 | 400 | 300 | 250 |
| Cy | 300 | 400 | 300 | 250 |
| Di | 250 | 400 | 300 | 250 |
Because the frame spans the whole partition, every row sees the same best/second/worst context - exactly what you want when annotating detail rows with group extremes. In PostgreSQL and SQL Server you can also add IGNORE NULLS to LAG, LEAD, and the value functions to skip over NULLs (handy for carrying the last non-null reading forward); MySQL does not support that modifier yet.
6. Aggregate window functions: running totals, moving averages, percent of total
Any aggregate - SUM, AVG, COUNT, MIN, MAX - becomes a window function the moment you add OVER. The frame decides whether you get a running, moving, or whole-partition value. Here is the whole toolkit in one query over the East region:
SELECT sale_day, amount,
SUM(amount) OVER w AS running_total,
COUNT(*) OVER w AS running_count,
AVG(amount) OVER (ORDER BY sale_day
ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS mov_avg_2,
ROUND(100.0 * amount /
SUM(amount) OVER (), 1) AS pct_of_all
FROM sales WHERE region = 'East'
WINDOW w AS (ORDER BY sale_day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
ORDER BY sale_day;| sale_day | amount | running_total | running_count | mov_avg_2 | pct_of_all |
|---|---|---|---|---|---|
| 2026-01-01 | 100 | 100 | 1 | 100.0 | 15.4 |
| 2026-01-02 | 200 | 300 | 2 | 150.0 | 30.8 |
| 2026-01-03 | 200 | 500 | 3 | 200.0 | 30.8 |
| 2026-01-04 | 150 | 650 | 4 | 175.0 | 15.4 |
Four different windows, one query: a cumulative sum and count (frame to current row), a two-row moving average (frame of 1 preceding + current), and a percent-of-total that divides each amount by the grand total from an empty OVER () - the whole partition. That last pattern, amount / SUM(amount) OVER (), is the cleanest "percent of total" you can write. For deeper aggregate patterns see the advanced SQL functions guide.
7. Practical recipes (query + result)
These are the patterns you will actually paste into real work. Each shows the query and the rows it returns.
Top-N per group
The canonical use of ROW_NUMBER: number rows within each region by amount descending, then keep number 1 (or ≤ N). Because window functions cannot appear in WHERE, wrap it in a subquery or CTE.
WITH ranked AS (
SELECT region, rep, amount,
ROW_NUMBER() OVER (PARTITION BY region
ORDER BY amount DESC, id) AS rn
FROM sales
)
SELECT region, rep, amount
FROM ranked WHERE rn = 1;| region | rep | amount |
|---|---|---|
| East | Ben | 200 |
| West | Di | 400 |
Month-over-month (period-over-period) change
Aggregate to the period first, then LAG the prior period and subtract. Here we compare consecutive days per region (the same shape works for months):
SELECT region, sale_day, amount,
LAG(amount) OVER (PARTITION BY region
ORDER BY sale_day) AS prev,
ROUND(100.0 * (amount -
LAG(amount) OVER (PARTITION BY region ORDER BY sale_day))
/ NULLIF(LAG(amount) OVER (PARTITION BY region
ORDER BY sale_day), 0), 1) AS pct_change
FROM sales WHERE region = 'West'
ORDER BY sale_day;| sale_day | amount | prev | pct_change |
|---|---|---|---|
| 2026-01-01 | 300 | NULL | NULL |
| 2026-01-02 | 300 | 300 | 0.0 |
| 2026-01-03 | 250 | 300 | -16.7 |
| 2026-01-04 | 400 | 250 | 60.0 |
NULLIF(prev, 0) guards against divide-by-zero when the previous period was zero.
Deduplicate with ROW_NUMBER
Keep one row per key and delete the rest. Number duplicates by a tiebreak, keep rn = 1, delete the others. Suppose rep + region should be unique:
WITH d AS (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY region, rep
ORDER BY id) AS rn
FROM sales
)
DELETE FROM sales
WHERE id IN (SELECT id FROM d WHERE rn > 1);This deletes ids 2, 4, 6, 8 (the second sale for each rep), keeping the earliest per rep. To preview instead of delete, swap the DELETE for a SELECT - always inspect rn > 1 rows before running the delete.
Quartiles / percentiles with NTILE
NTILE(4) buckets all rows into four groups of near-equal size - quartiles. Bucket 1 is the top (or bottom) quarter depending on your sort:
SELECT rep, amount,
NTILE(4) OVER (ORDER BY amount DESC) AS quartile,
ROUND(PERCENT_RANK() OVER (ORDER BY amount), 2) AS pct_rank
FROM sales
ORDER BY amount DESC;| rep | amount | quartile | pct_rank |
|---|---|---|---|
| Di | 400 | 1 | 1.00 |
| Cy | 300 | 1 | 0.71 |
| Cy | 300 | 2 | 0.71 |
| Di | 250 | 2 | 0.57 |
| Ben | 200 | 3 | 0.29 |
| Ana | 200 | 3 | 0.29 |
| Ben | 150 | 4 | 0.14 |
| Ana | 100 | 4 | 0.00 |
PERCENT_RANK and CUME_DIST give continuous percentile positions; NTILE gives discrete buckets. For true statistical percentiles use the ordered-set aggregates PERCENTILE_CONT and PERCENTILE_DISC (PostgreSQL and SQL Server; MySQL lacks them).
Running total that resets per group
Combine PARTITION BY with an explicit ROWS frame to get a cumulative total that restarts at each rep - useful for "cumulative sales this rep has booked" columns in dashboards:
SELECT rep, sale_day, amount,
SUM(amount) OVER (
PARTITION BY rep
ORDER BY sale_day
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS rep_running
FROM sales
ORDER BY rep, sale_day;| rep | sale_day | amount | rep_running |
|---|---|---|---|
| Ana | 2026-01-01 | 100 | 100 |
| Ana | 2026-01-02 | 200 | 300 |
| Ben | 2026-01-03 | 200 | 200 |
| Ben | 2026-01-04 | 150 | 350 |
| Cy | 2026-01-01 | 300 | 300 |
| Cy | 2026-01-02 | 300 | 600 |
| Di | 2026-01-03 | 250 | 250 |
| Di | 2026-01-04 | 400 | 650 |
The total restarts at each new rep because the partition boundary clears the frame. Swap SUM for COUNT(*) and you have a per-rep cumulative deal counter with zero extra effort.
Gaps and islands (brief)
A classic: find runs of consecutive values (islands) and the breaks between them (gaps). The trick is that value - ROW_NUMBER() stays constant within a consecutive run, so you group by that difference:
-- Group consecutive days into islands
SELECT region, MIN(sale_day) AS start_day,
MAX(sale_day) AS end_day, COUNT(*) AS days
FROM (
SELECT region, sale_day,
DATE_SUB(sale_day, INTERVAL
ROW_NUMBER() OVER (PARTITION BY region ORDER BY sale_day)
DAY) AS grp
FROM sales
) t
GROUP BY region, grp;Every day in our sample is consecutive, so each region collapses to a single island spanning 2026-01-01 to 2026-01-04. Insert a missing day and you would see two islands with the gap between them. This pattern generalises to detecting streaks, sessions, and downtime windows.
8. Performance and gotchas
Window functions are usually faster and always more readable than the correlated-subquery or self-join equivalents they replace. A correlated subquery re-scans the table once per row (O(n²) in the worst case); a window function sorts once and streams. The chart below is a representative comparison for a "running total / rank per group" task on a mid-size table.
Percentages are representative, not benchmarks - your numbers depend on indexes, row counts, and the engine. Measure with EXPLAIN on your own data. A few concrete tips:
Support the sort
The PARTITION BY + ORDER BY inside OVER drives a sort. An index on (partition_cols, order_cols) can let the engine skip it entirely.
Filter early
Window functions run after WHERE but you cannot filter on them in the same query level. Push row-reducing predicates into WHERE; filter on the window result in an outer query or CTE.
Be explicit about frames
Write ROWS frames for running totals to avoid the RANGE peer surprise, and to help the optimizer.
Reuse windows
Use a named WINDOW clause so multiple functions share one sort instead of forcing several.
Watch out for NULLs and ordering: if your ORDER BY column has NULLs, their position differs by engine (use NULLS FIRST / NULLS LAST in PostgreSQL). And if the ORDER BY inside OVER is not unique, ROW_NUMBER and running-total order among ties are non-deterministic - add a tiebreaker column.
Because window functions cannot be used in WHERE, GROUP BY, or HAVING (they are evaluated after those clauses), the CTE/subquery wrapper is not optional - it is the standard idiom. For a wider look at rewriting slow queries, see advanced SQL queries and the performance tuning guide.
9. Database support notes
Window functions are ANSI SQL and are broadly supported, but versions and edge features vary. The essentials:
| Feature | MySQL 8+ | PostgreSQL | SQL Server |
|---|---|---|---|
| Basic OVER / PARTITION / ORDER | Yes (8.0+) | Yes (8.4+) | Yes (2005+) |
| ROWS / RANGE frames | Yes | Yes | Yes (2012+) |
| LAG / LEAD / FIRST/LAST_VALUE | Yes (8.0) | Yes | Yes (2012+) |
| NTILE / RANK / DENSE_RANK | Yes (8.0) | Yes | Yes |
| GROUPS frame mode | No | Yes (11+) | No |
| PERCENTILE_CONT / _DISC | No | Yes | Yes (2012+) |
| Named WINDOW clause | Yes (8.0) | Yes | Yes (2022+) |
| FILTER (WHERE ...) on aggregates | No | Yes | No |
The headline: if you are on MySQL 5.7 or older, window functions do not exist - you must upgrade to 8.0 or emulate them with variables or self-joins. On 8.0+, PostgreSQL, or a supported SQL Server version, everything in this guide runs with at most trivial syntax tweaks (date arithmetic differs: DATE_SUB in MySQL, date - INTERVAL in PostgreSQL, DATEADD in SQL Server).
Takeaway: master four ideas - keep-every-row semantics, PARTITION BY, ORDER BY inside OVER, and explicit ROWS frames - and every ranking, offset, and running-aggregate pattern in this guide falls out naturally.
Keep this page bookmarked as a reference. To go deeper, pair it with the SQL joins guide (since top-N-per-group and gaps-and-islands often combine joins with windows), the printable SQL cheat sheet, and the hands-on SQL advanced course where you build these queries against real data.