Ask a junior analyst for “each salesperson’s sales, plus their share of their region’s total”, and you will often see two queries glued together with a join, or a spreadsheet with a pasted subtotal. Window functions solve this kind of problem in one step. Once the idea clicks, a whole category of analytics questions becomes much easier.

The difference from GROUP BY

An aggregate with GROUP BY collapses rows. If you group sales by region and sum them, ten thousand order rows become five region rows. The individual orders are gone from the result.

A window function calculates across a set of related rows but keeps every row. Each order stays in the output, and alongside it you can show the region total, the order’s rank within the region, or the running total up to that date. The “window” is simply the set of rows the calculation looks at for each row.

The three parts of a window

Every window function uses an OVER clause, which can contain up to three pieces:

  • PARTITION BY splits rows into groups, such as one per region. Calculations restart for each group. Leave it out and the whole result is one group.
  • ORDER BY sets the sequence within each group, such as by order date. Ranking and running totals depend on it.
  • A frame, written with ROWS or RANGE, narrows the window further, for example “the current row and the two before it” for a three-period moving average.

So “the sum of amount over a partition by region” gives each row its region’s total, while “the sum of amount over a partition by region, ordered by order date” gives a running total that grows row by row within each region.

Ranking: three functions that look alike

Suppose four salespeople in a region have sales of 90, 80, 80 and 70 lakh. The three ranking functions treat the tie differently:

  • ROW_NUMBER gives 1, 2, 3, 4. Ties are broken arbitrarily unless you add a tiebreaker to the ordering.
  • RANK gives 1, 2, 2, 4. Tied rows share a rank and the next rank is skipped.
  • DENSE_RANK gives 1, 2, 2, 3. Tied rows share a rank and nothing is skipped.

The choice matters for questions like “top two per region”. With RANK or DENSE_RANK, the tie at second place returns three people. With ROW_NUMBER, exactly two come back, but which of the tied pair is included may vary between runs unless you add a tiebreaker. Neither is wrong; the business needs to decide which answer it wants.

Comparing a row to its neighbours

LAG and LEAD return a value from an earlier or later row in the same window. For monthly revenue ordered by month, LAG of revenue gives last month’s figure on this month’s row, so month-on-month growth becomes a simple subtraction or division. Before window functions, this needed a self-join that was slow and easy to get wrong at the edges, such as the first month of each year.

Common mistakes

  • Filtering on a window result in WHERE. Window functions are calculated after WHERE, so you cannot filter on them there. Wrap the query in a subquery or a common table expression, then filter the outer query.
  • Forgetting the default frame. When ORDER BY is present and no frame is given, many databases default to a frame from the start of the partition to the current row, treating tied values as peers. That is why a running total can jump by two rows at once on a tied date. Specifying ROWS explicitly avoids the surprise.
  • Using a window where an aggregate is enough. If you only need region totals, a plain GROUP BY is clearer and often faster.

Beyond SQL

The same idea exists elsewhere under other names. In pandas, groupby combined with transform, cumsum, rank or shift does the same jobs. In Power BI, DAX functions such as RANKX and the newer window functions cover similar ground. Learning the concept once in SQL makes all of these easier to pick up.

Written by the RP Strativa Team. Exact syntax and default frame behaviour vary slightly between PostgreSQL, SQL Server, MySQL 8 and other databases.

Leave a Reply

Trending