Skip to content
Tutorials Center
My learning

Topics

Lesson 25 of 38 ยท Window Functions and Analytics

Ranking, Offsets and Running Totals

Use ROW_NUMBER, RANK, DENSE_RANK, NTILE, LAG, LEAD and cumulative sums in DuckDB to rank rows, find top-N per group and compare each row with its neighbours.

Most analytic questions fall into three buckets: who is first (ranking), how did it change from the last time (offsets), and how much so far (running totals). Window functions answer all three. The SQL here is standard, so you can run every example.

Ranking functions

Function Behaviour on ties
ROW_NUMBER() always unique: 1, 2, 3, 4
RANK() ties share a rank, then a gap: 1, 2, 2, 4
DENSE_RANK() ties share a rank, no gap: 1, 2, 2, 3
PERCENT_RANK() (rank - 1) / (rows - 1), from 0 to 1
CUME_DIST() fraction of rows up to and including this one
NTILE(n) split rows into n roughly equal buckets
SQL Compare the ranking functions

Press Run (or Ctrl + Enter) to see the result here.

Ben and Cy tie on 85 in chess. ROW_NUMBER still numbers them 2 and 3 (an arbitrary order unless you add a tie-breaker such as player), RANK gives both 2 and jumps to 4, and DENSE_RANK gives both 2 and continues with 3. The WINDOW w AS (...) clause names a window for reuse; more in the next lesson.

Top N per group

"The best two scores per game" is the question that makes people reach for window functions. In portable SQL you rank in a subquery and filter outside:

SQL Top 2 per group (portable subquery)

Press Run (or Ctrl + Enter) to see the result here.

DuckDB lets you drop the subquery with QUALIFY (read-only here, see the next lesson):

SQL
SELECT game, player, pts
FROM scores
QUALIFY ROW_NUMBER() OVER (PARTITION BY game ORDER BY pts DESC, player) <= 2;

Choose the function by what you want to happen on ties: ROW_NUMBER for exactly N rows, RANK or DENSE_RANK to include everyone tied for a place.

LAG and LEAD

LAG(col, n, default) reads a value from n rows before the current row in the window order; LEAD reads from rows after. The default for n is 1 and the default result when no such row exists is NULL.

SQL Day-over-day change with LAG

Press Run (or Ctrl + Enter) to see the result here.

The first row has no previous day, so prev_day, change and pct_change are NULL. Give LAG a default (LAG(visitors, 1, 0)) if you prefer zero. Offsets are perfect for finding gaps ("days between orders"), detecting changes, and comparing with the same period last week (LAG(x, 7)).

FIRST_VALUE, LAST_VALUE and NTH_VALUE

These read a value at a given position of the frame. Because the default frame ends at the current row, LAST_VALUE surprises beginners: it returns the current row's own value. Widen the frame:

SQL
LAST_VALUE(price) OVER (
  PARTITION BY product ORDER BY ts
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)

DuckDB also supports IGNORE NULLS for FIRST_VALUE, LAST_VALUE, LAG, LEAD and NTH_VALUE, which is the standard way to forward-fill missing readings: LAST_VALUE(reading IGNORE NULLS) OVER (ORDER BY ts).

Running totals, averages and extremes

Combine an aggregate with ORDER BY for cumulative figures, or with an explicit frame for moving ones:

SQL Running total, running max and 3-day moving average

Press Run (or Ctrl + Enter) to see the result here.

The moving average uses ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, three rows ending at this one. For the first two days fewer rows exist, so the average uses what is available. Frames get a full treatment in the next lesson.

Percentiles and distribution

NTILE(4) splits rows into quartiles; PERCENT_RANK() and CUME_DIST() give a smooth position. For exact percentile values DuckDB offers QUANTILE_CONT(x, 0.5) OVER (...) and MEDIAN(x) OVER (...) (read-only here), which are not in every database.

Common mistakes

  • Ranking without a tie-breaker, then getting different results from run to run.
  • Filtering rn <= 2 in the same SELECT that computes it; the alias is not visible in WHERE.
  • Using LAST_VALUE with the default frame.
  • Dividing by LAG(...) when it can be 0 or NULL; use NULLIF.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Scores 90, 85, 85, 70 ranked descending. What does DENSE_RANK give?

    DENSE_RANK gives ties the same rank and does not leave a gap afterwards.
  2. 2. What does LAG(x) return on the first row of a partition?

    There is no earlier row, so the result is NULL unless you pass a default as the third argument.
  3. 3. Which function guarantees exactly N rows per group when you filter rn <= N?

    ROW_NUMBER never repeats a number, so it yields exactly N rows.
  4. 4. Why does LAST_VALUE with the default frame often return the current row's value?

    With ORDER BY the frame runs to the current row, so the last row of the frame is the current one. Use UNBOUNDED FOLLOWING.

Summary

  • ROW_NUMBER, RANK and DENSE_RANK differ only in how ties are treated; add a tie-breaker.
  • Top-N per group = rank in a subquery (or QUALIFY in DuckDB) and filter.
  • LAG and LEAD look at neighbouring rows; defaults fill the edges.
  • Aggregates with ORDER BY or an explicit ROWS frame give running and moving figures.

Next: QUALIFY, Named Windows and Frames