Hamropedia

Phase 5 of 8 · 5 lessons

Window functions: ranking, running totals and comparing rows

Calculate across related rows — rank them, compare each with the one before, keep running totals — without losing the individual rows the way GROUP BY does.

Window functions are the single most tested "advanced" SQL topic. Top N per group, month-on-month growth, running totals, streaks — all of them become short once you think in windows: for each row, look at a set of related rows, and work something out.

By Manoj Kumar Thapa

AI Software Engineer ·

0 of 5 done

Lessons, in order

  1. 5.1

    What a window function is: OVER and PARTITION BY

    Must know

    A window function calculates a value for each row from a set of related rows (its "window"), defined by OVER (PARTITION BY … ORDER BY …), and — unlike GROUP BY — keeps every row in the result.
  2. 5.2

    Ranking: ROW_NUMBER, RANK and DENSE_RANK

    Must know

    ROW_NUMBER numbers rows 1, 2, 3, 4 with no ties; RANK gives ties the same number and then skips (1, 2, 2, 4); DENSE_RANK gives ties the same number without skipping (1, 2, 2, 3).
  3. 5.3

    LAG and LEAD: comparing with the previous or next row

    Must know

    LAG(column) returns the value from the previous row and LEAD(column) the value from the next row, in the order set by OVER (… ORDER BY …), so a row can be compared with its neighbour.
  4. 5.4

    Running totals and moving averages: window frames

    Must know

    With ORDER BY inside OVER, SUM and AVG become running totals; a frame such as ROWS BETWEEN 2 PRECEDING AND CURRENT ROW limits the window to nearby rows, giving a moving average.
  5. 5.5

    Share of total, NTILE and FIRST_VALUE

    Should know

    x / SUM(x) OVER () gives each row's share of the total; NTILE(n) splits ordered rows into n roughly equal buckets; FIRST_VALUE and LAST_VALUE pull a value from the first or last row of the window.