Window Functions: ROW_NUMBER, RANK & DENSE_RANK

Aggregate functions collapse many rows into one summary, but sometimes you need a summary alongside every row, not instead of them. Window functions do exactly that: they compute a value across a set of related rows while keeping each individual row in the output. This lets you rank batsmen, number rows, and compute running totals without losing the detail rows you started with.

The ranking window functions, ROW_NUMBER, RANK, and DENSE_RANK, are the perfect entry point. Each assigns a position to every row within an ordered group, but they handle ties differently, and choosing the wrong one quietly produces a misleading leaderboard. Understanding these three is essential because ranking is one of the most common and most subtly error-prone tasks in all of analytics.

Window functions are what separate basic SQL from genuinely analytical SQL. Questions like "each player's rank within their team" or "the top three scorers per venue" are awkward or impossible with grouping alone, yet they fall out naturally from window functions. Mastering them unlocks the per-row analytical power that real reporting, leaderboards, and data science workflows depend on every day.

Analogy🏏Cricket

🏏 Think of it like cricket: Imagine the end of an IPL innings when the scorer announces the team's final total. Just as the scorer does not read out all one hundred and twenty individual deliveries but instead sums them into a single team score, SUM folds many row values into one number. Just as the commentator reports the highest individual score, the average partnership, and the number of wickets, MAX, AVG, and COUNT each summarise the innings differently. Just as a not-out batsman with no deliveries faced does not lower the team average, aggregates skip NULLs when computing. Just as the announcement is one figure no matter whether the innings had fifty deliveries or two hundred and fifty, an aggregate collapses any number of rows into a single result. This reveals why summaries answer the questions about the whole that individual deliveries cannot.