Window Functions

RANK, ROW_NUMBER, and running totals — calculations across rows without collapsing them

Knowledge0%

Learn & Drill

Fluency0%

Drill & Speed

Retention0%

Mastery & Review

Confidence0%

All modes

Practice

Free tier: read the explanation here. Upgrade to Pro for Drills, Speed challenges & Mastery badges.

Upgrade

Knowledge

Fluency

Retention

Knowledge Debt detected

You can study this freely — but your score may plateau if these foundations have gaps. The Mastery badge requires them to be solid.

Subqueries & CASE WHEN0%

Explanation

A window function computes a value across a set of related rows ("a window") — but unlike GROUP BY, it does not collapse those rows into one. Every input row stays in the output, with an extra computed column.

Basic syntax: function() OVER (PARTITION BY ... ORDER BY ...)

sql

-- Rank claims by amount, within each payer
SELECT payer, id, amount,
  RANK() OVER (PARTITION BY payer ORDER BY amount DESC) AS rank_in_payer
FROM claims;

PARTITION BY vs GROUP BY:

GROUP BY collapses rows — one output row per group
PARTITION BY keeps every row, but resets the calculation for each group

ORDER BY inside OVER() controls the order rows are processed for ranking, running totals, LAG/LEAD, etc. It does NOT sort the final result (use a separate outer ORDER BY for that).

RANK vs DENSE_RANK vs ROW_NUMBER — how they handle ties: ``sql -- amounts: 100, 100, 90 -- RANK(): 1, 1, 3 (skips 2 after a tie) -- DENSE_RANK(): 1, 1, 2 (no gaps) -- ROW_NUMBER(): 1, 2, 3 (always unique, ties broken arbitrarily)

Running total — SUM() OVER with ORDER BY accumulates row by row: ``sql SELECT submitted_date, amount, SUM(amount) OVER (ORDER BY submitted_date) AS running_total FROM claims;

LAG / LEAD — look at the previous/next row's value without a self-join: ``sql SELECT submitted_date, amount, LAG(amount) OVER (ORDER BY submitted_date) AS previous_amount, amount - LAG(amount) OVER (ORDER BY submitted_date) AS change FROM claims;

Examples

Rank claims within each payer

All rows are returned (no collapsing). Each payer group restarts its ranking at 1.

SELECT payer, id, amount,
  RANK()       OVER (PARTITION BY payer ORDER BY amount DESC) AS rank_,
  DENSE_RANK() OVER (PARTITION BY payer ORDER BY amount DESC) AS dense_rank_,
  ROW_NUMBER() OVER (PARTITION BY payer ORDER BY amount DESC) AS row_num
FROM claims;

Running total of claims over time

Each row shows the cumulative sum up to and including that row's date.

SELECT submitted_date, amount,
  SUM(amount) OVER (ORDER BY submitted_date) AS running_total
FROM claims
ORDER BY submitted_date;

How well did you understand this?

Next in SQL & Databases

Indexes & Performance

Continue