When should you NOT use SQL Window Functions?

Avoid window functions when a simple GROUP BY aggregate answers the question — window functions have higher planning cost. Do not use them on tables without an appropriate index on the PARTITION BY and ORDER BY columns — the sort cost dominates.

When is SQL Window Functions the right choice?

Use ROW_NUMBER or RANK when you need per-partition rankings without losing the underlying rows (top N per category). Use LAG/LEAD to compare a row with its predecessor or successor in a time series without a self-join. Use SUM/AVG OVER for running totals, moving averages, and cumulative distributions in reporting queries.

← Back to glossary

SQL Window Functions

database Intermediate

TL;DR

Window functions (ROW_NUMBER, RANK, LAG, LEAD, SUM OVER) perform calculations across a set of rows related to the current row without collapsing them into a single aggregate — enabling rankings, running totals, and comparisons within result sets.

Explanation

Unlike GROUP BY aggregates which reduce multiple rows to one, window functions operate over a defined frame of rows (the window) and return a value for every input row. The OVER clause defines the window via PARTITION BY (grouping), ORDER BY (row order within partition), and an optional ROWS/RANGE frame. Common functions include ROW_NUMBER (unique sequential rank), RANK/DENSE_RANK (tied ranks), LAG/LEAD (access previous/next row), and aggregate functions like SUM, AVG, MAX applied as running calculations. Window functions execute after WHERE and GROUP BY but before ORDER BY and LIMIT, making them composable with other clauses. In PHP applications they eliminate the need for multiple round trips or application-side sorting and ranking logic.

Watch Out

⚠ Window functions are evaluated after WHERE and JOIN but before the outer SELECT aliases are available — you cannot reference a window function result in the same SELECT's WHERE clause. Wrap it in a CTE or subquery.

Common Misconception

✗ Window functions do not filter or reduce rows — every input row produces an output row, even when calculating aggregates like SUM OVER.

Why It Matters

Replacing application-side ranking and running-total loops with a single window function query reduces round trips and moves computation to where the data lives — orders of magnitude faster on large result sets.

Common Mistakes

Omitting ORDER BY inside OVER for RANK or ROW_NUMBER — the result is non-deterministic.
Using PARTITION BY when GROUP BY is intended — window functions do not reduce rows, so counts stay per-row.
Trying to filter on a window function alias in WHERE — use a subquery or CTE; window functions are not available in WHERE.

Avoid When

Avoid window functions when a simple GROUP BY aggregate answers the question — window functions have higher planning cost.
Do not use them on tables without an appropriate index on the PARTITION BY and ORDER BY columns — the sort cost dominates.

When To Use

Use ROW_NUMBER or RANK when you need per-partition rankings without losing the underlying rows (top N per category).
Use LAG/LEAD to compare a row with its predecessor or successor in a time series without a self-join.
Use SUM/AVG OVER for running totals, moving averages, and cumulative distributions in reporting queries.

Code Examples

💡 NoteThe bad example loops in PHP assigning sequential ranks — it ignores ties and requires loading every user. The window function ranks in one query, handles ties correctly, and adds a running total column at no extra cost.

✗ Vulnerable

-- Application-side ranking: N+1 queries
$users = $db->query('SELECT * FROM users ORDER BY score DESC');
$rank = 1;
foreach ($users as $u) {
    $u['rank'] = $rank++; // sequential but ignores ties
}

✓ Fixed

-- Single query with window function:
SELECT
    id,
    name,
    score,
    RANK() OVER (ORDER BY score DESC) AS rank,
    SUM(score) OVER ()               AS total_score
FROM users;

References

↗ https://www.postgresql.org/docs/current/tutorial-window.html