Window Functions: Calculations Without Collapsing

Concept. Window functions compute aggregates across a partition defined by OVER, returning one result per input row instead of collapsing them like GROUP BY. The OVER PARTITION BY syntax adds derived metrics while preserving row granularity.

Intuition. AVG(rating) OVER (PARTITION BY user_id) computes Mickey's average alongside every one of Mickey's listen rows without collapsing them. The Listens table comes back at 9 rows, plus one new column.

GROUP BY vs PARTITION BY

Two neutral panels over the same nine Listens rows: three Mickey (blue) 4.5, 4.2, 3.9; three Minnie (orange) 4.7, 4.6, 3.9; three Daffy (purple) 2.9, 4.9, NULL. Left, GROUP BY: the nine rows collapse into three summary rows, one average per user (Mickey 4.2, Minnie 4.4, Daffy 3.9). Right, PARTITION BY: all nine rows are kept, each gaining a user_avg column with that user's average. Color key: blue is Mickey, orange is Minnie, purple is Daffy.

Figure 1. The same per-user average, two ways. GROUP BY collapses the nine listen rows into one summary row per user (Mickey 4.2, Minnie 4.4, Daffy 3.9), losing the detail. A window function, AVG(rating) OVER (PARTITION BY user_id), keeps all nine rows and attaches each user's average as a new column. Same math, but the window preserves row granularity.

GROUP BY: Collapses Rows

GROUP BY collapses the detail rows into one summary row per group.

-- One row per user; the per-row Listens detail is gone.
SELECT user_id, AVG(rating) AS avg_rating
FROM Listens
GROUP BY user_id;

9 rows → 3 rows. Mickey 4.2, Minnie 4.4, Daffy 3.9. Daffy's average ignores the NULL: AVG(2.9, 4.9, NULL) = 7.8 / 2 = 3.9.

PARTITION BY: Keep All Rows

PARTITION BY splits rows into groups, computes the aggregate within each group, and keeps every original row.

-- All 9 listen rows preserved, with each user's avg attached as a new column.
SELECT user_id, song_id, rating,
       AVG(rating) OVER (PARTITION BY user_id) AS user_avg
FROM Listens
ORDER BY user_id, song_id;

9 rows → 9 rows (all kept). Each row keeps its rating and gains its user's average as a new user_avg column (Mickey 4.2, Minnie 4.4, Daffy 3.9). Daffy's NULL-rated row still shows 3.9: the window's AVG ignores the NULL, just as GROUP BY did.

Ranking Functions

Window functions can rank rows without collapsing them. Start with one function, RANK. It adds a rank column, ordered by rating within each user, highest first:

The RANK query, then the three steps side by side. Step 1, PARTITION BY user_id: three lanes in original order. Step 2, ORDER BY rating DESC: each lane sorted high to low with NULL last, so Daffy's 4.9 moves above 2.9. Step 3, assign the rank: each lane numbered 1, 2, 3 from the top.

-- Rank each user's listens by rating, highest first.
SELECT
  user_id,
  song_id,
  rating,
  RANK() OVER (PARTITION BY user_id ORDER BY rating DESC) AS RANK
FROM Listens;

That is ranking: partition, then order, then number.

Handling ties

RANK is one of three ranking functions. With no duplicate ratings they all produce the numbers above, so the choice only matters on a tie. The full query asks for all three at once:

SELECT user_id, song_id, rating,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY rating DESC) AS ROW_NUMBER,
  RANK()       OVER (PARTITION BY user_id ORDER BY rating DESC) AS RANK,
  DENSE_RANK() OVER (PARTITION BY user_id ORDER BY rating DESC) AS DENSE_RANK
FROM Listens;

To see them diverge, imagine Mickey had a 4th listen rated 4.5, a tie with his existing 4.5:

Mickey's listens sorted by rating descending, with a hypothetical tie of two 4.5 ratings. Four rows: 4.5, 4.5, 4.2, 3.9. ROW_NUMBER is 1, 2, 3, 4, always unique. RANK is 1, 1, 3, 4: the tie shares rank 1, then RANK skips to 3 leaving a gap. DENSE_RANK is 1, 1, 2, 3: the tie shares rank 1, then DENSE_RANK stays packed with no gap. The two tied rows are shaded. Color key: blue is Mickey, the shaded band is the tie.

Figure 2. The three ranking functions agree until a tie. ROW_NUMBER is always unique (1, 2, 3, 4). RANK lets the tie share a rank then skips, leaving a gap (1, 1, 3, 4). DENSE_RANK lets the tie share a rank but stays packed, no gap (1, 1, 2, 3). The shaded band is Mickey's hypothetical 4.5 tie.

Three answers to one question:

  • ROW_NUMBER: every row gets a unique number, ties broken arbitrarily. Use it for de-duplication and pagination.

  • RANK: ties share a rank, then it skips, leaving a gap. Use it for leaderboards where "joint 2nd, then 4th" is correct.

  • DENSE_RANK: ties share a rank with no gap. Use it when the next distinct value should be the next number.

Key Rules

What makes a function a window function is the OVER clause. AVG(rating) is GROUP BY style and collapses to one row per group. AVG(rating) OVER (PARTITION BY user_id) is a window function: one value per row, all rows kept. Any aggregate works in a window, plus LAG and LEAD to see the previous or next row.

  1. No Row Reduction: Unlike GROUP BY, all rows survive.

  2. PARTITION BY: Defines groups (optional - omit for whole table).

  3. ORDER BY: Sets sequence within partitions (required for some functions).

  4. NULL Handling: NULLs group together in PARTITION BY, sort first/last in ORDER BY.

Common Patterns

Five common window-function patterns sharing the OVER skeleton: ranking with ROW_NUMBER, RANK and DENSE_RANK; running totals with SUM OVER ORDER BY; moving averages with AVG over a ROWS window; LAG and LEAD to compare to the previous or next row; and NTILE or PERCENT_RANK for percentiles.

Common Mistakes

Three common window-function mistakes, all silent. Missing PARTITION BY ranks across the whole table instead of per group. Wrong ORDER BY direction flips the ranking, so ASC makes your worst row number one. Forgetting NULLs in ORDER BY: NULLs sort first or last depending on the database, Postgres first and MySQL last.