Window functions — ROW_NUMBER, RANK, DENSE_RANK

Window functions perform calculations across a set of rows related to the current row — without collapsing those rows into a single output row the way GROUP BY aggregates do. They were standardised in SQL:2003 and are now supported by every major analytical database. The defining feature is the OVER() clause: it specifies the window — the partition and ordering — over which the function operates. Window functions let you compute running totals, rankings, moving averages, and lead/lag comparisons while keeping every individual row in the result set visible alongside its computed value.

Before window functions, analysts computed rankings and running totals using correlated subqueries — an approach that scales catastrophically with data volume. Computing the rank of each player in a 10-million-row delivery table required a subquery that re-scanned the table for every row, turning a linear-time problem into a quadratic one. Window functions replace these correlated subqueries with a single linear pass over the partitioned, sorted data, making analytical queries that were previously impractical on large tables routine.

For data engineers, window functions are indispensable in transformation layers. Deduplication (ROW_NUMBER to keep the latest record per entity), session analysis (detecting gaps between events), time-series interpolation (filling missing values from the previous non-null row), and slowly changing dimension (SCD) logic all depend on window functions. Mastering the PARTITION BY and ORDER BY clauses of the OVER() specification is the primary skill required to use window functions correctly and efficiently.

Analogy🏏Cricket

🏏 Think of it like cricket: A SELECT query is precisely how a selection committee picks a playing XI. FROM is the full list of centrally contracted players — the raw pool. WHERE is the fitness and eligibility screen: injured or unavailable players are removed before anyone debates merit, and the fewer names that survive this screen, the faster the meeting goes — exactly why a good WHERE clause matters more than anything downstream. ORDER BY is ranking the survivors by recent form, then by experience as the tiebreaker. LIMIT 11 takes the top of that ranked list and stops. The committee never ranks the entire national player pool and then discards thousands of names — and neither should your query force the database to sort millions of rows it will immediately throw away. The order of operations is the whole game: filter first, sort what remains, take only what you need.