Skip to content
Tutorials Center
My learning

Topics

Lesson 11 of 33 ยท Querying

Window Functions

Learn SQLite window functions: OVER, PARTITION BY, ROW_NUMBER, RANK, running totals, LAG and LEAD, frames and named windows, with live examples.

GROUP BY collapses rows: ten books become one row per genre. A window function does the opposite: it computes something across a set of related rows but keeps every row in the result. That makes it the right tool for "each book next to its genre average", "rank the books by price inside each genre", "running total of orders" and "days since the customer's previous order".

SQLite has supported window functions since version 3.25 (2018), and its implementation is complete enough to be a good place to learn them. This lesson covers the OVER clause, partitions, ordering, the ranking and value functions, frames and named windows. For the broader theory, the SQL tutorial has a whole chapter, starting with window functions.

Anatomy of OVER

PARTITION BY Split rows into independent groups.
ORDER BY Put rows in order inside each group.
Frame Which nearby rows the function sees.

Leave any part out and the default applies.

1 / 2

An aggregate that keeps the rows

Add OVER (...) after any aggregate and it becomes a window function. An empty OVER () means "the whole result set is one window", so every row sees the same average:

SQL Each book against the overall average ยท database: bookstore

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

All four rows show the same average (14.83) next to their own price, and difference says how far each book is from it. Doing this with GROUP BY would need a join to a subquery; with a window function it is one expression.

Window functions run after WHERE, GROUP BY and HAVING, and before ORDER BY and LIMIT. As a consequence you cannot use a window function directly in a WHERE clause: wrap the query in a subquery or CTE and filter outside, as the top-N example below does.

PARTITION BY and ranking

PARTITION BY restarts the calculation for each group. Combined with ORDER BY inside the window, the ranking functions number the rows:

  • ROW_NUMBER(): 1, 2, 3, ... with no ties (ties are broken arbitrarily unless the ORDER BY is unique).
  • RANK(): tied rows share a rank and the next rank skips numbers (1, 2, 2, 4).
  • DENSE_RANK(): tied rows share a rank and no numbers are skipped (1, 2, 2, 3).
  • NTILE(n): splits the partition into n buckets of nearly equal size.

The difference between the first three only shows when there are ties, so use a small table with deliberate ones:

SQL ROW_NUMBER, RANK and DENSE_RANK

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

Bo and Cy tie on 85. RANK gives them both 2 and jumps to 4 for Di; DENSE_RANK gives Di 3; ROW_NUMBER simply counts 1 to 4, using the player name to break the tie.

Now the most common real-world pattern, top N per group. Number the rows inside each genre by price, then keep rank 1 in an outer query:

SQL Most expensive book in every genre ยท database: bookstore

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

Change rn = 1 to rn <= 2 for the top two per genre. Use RANK() instead of ROW_NUMBER() if you want ties for first place to all appear.

Running totals and frames

When a window has an ORDER BY, aggregates become cumulative. By default the frame (the rows the function can see) runs from the first row of the partition up to the current row, including rows that tie with it. That produces a running total:

SQL Running total and moving sum ยท database: bookstore

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

After five orders the running total is 8. last_three_orders uses an explicit frame, ROWS BETWEEN 2 PRECEDING AND CURRENT ROW: the current row and the two before it. For the fourth order that is rows 2 to 4, with quantities 2, 1 and 3, which add up to 6, the value the column shows.

Two details about frames:

  1. With ORDER BY ordered_on alone, rows that share a date are peers and are all included at once (the default RANGE frame). Adding the unique id as a tie-breaker, as above, gives a strictly row-by-row running total. Without it, two orders on the same day would both show the same total.
  2. Frame units are ROWS (count rows), RANGE (compare values) and GROUPS (count peer groups). The window frames lesson in the SQL tutorial compares them in detail, and SQLite supports all three plus EXCLUDE.

LAG, LEAD and the value functions

LAG(expr, n, default) reads the value from n rows before the current row (default 1), and LEAD reads from rows after. Both return NULL when there is no such row, unless you pass a default. They ignore frames; only partition and order matter.

SQL Days between a customer's orders ยท database: bookstore

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

The WINDOW w AS (...) clause defines a named window once so several functions can share it. Customer 2 ordered on 2024-03-02 and again on 2024-06-12, which is 102 days later; customer 3's gap is 15 days. The first order of every customer has no previous_order, so days_since is NULL there.

FIRST_VALUE(expr), LAST_VALUE(expr) and NTH_VALUE(expr, n) pick a value from the frame. They hide a classic trap: with ORDER BY the default frame stops at the current row, so LAST_VALUE simply returns the current row's own value. To get the true last row, widen the frame:

SQL LAST_VALUE needs a full frame ยท database: bookstore

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

last_default repeats each row's own title, while priciest is The Quiet Algorithm on every row.

Percent of group and named windows

A named window can also be extended: RANK() OVER (w ORDER BY price DESC) takes the partition from w and adds an ordering. This shows each book's share of its genre's total and its rank inside the genre:

SQL Share of genre revenue potential ยท database: bookstore

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

The Fiction books total 44.74, so Rivers of Glass is 36.9 percent of it. Children has one book, so it is 100 percent.

What SQLite supports

  • Ranking: ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE.
  • Offset and value: LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE.
  • Any built-in aggregate with OVER, including group_concat; user-defined aggregates may also support it.
  • Frames: ROWS, RANGE, GROUPS, EXCLUDE, FILTER (WHERE ...) on aggregates, and WINDOW definitions.
  • Not available: IGNORE NULLS / RESPECT NULLS on LAG, LEAD and the value functions, and window functions inside WHERE, GROUP BY or HAVING.

Window functions can be expensive over large unsorted tables, because the engine must sort each partition. An index that matches the PARTITION BY and ORDER BY columns lets SQLite skip that sort; see the indexes lesson.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What is the main difference between a window function and GROUP BY?

    A window function computes over related rows but still returns one result row per input row.
  2. 2. Scores are 90, 85, 85, 70 (descending). What does RANK() give the last row?

    RANK leaves a gap after ties. Two rows share rank 2, so the next rank is 4. DENSE_RANK would give 3.
  3. 3. Why can't you write WHERE ROW_NUMBER() OVER (...) = 1?

    WHERE runs before window functions. Compute the number in a subquery, then filter on it outside.
  4. 4. With ORDER BY price and no frame, what does LAST_VALUE(title) return?

    The default frame ends at the current row, so the last row of the frame is the current row. Use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING for the true last row.

Summary

  • OVER turns an aggregate or ranking function into a window function that keeps all rows.
  • PARTITION BY creates independent groups; ORDER BY inside OVER orders them and makes aggregates cumulative.
  • ROW_NUMBER, RANK and DENSE_RANK differ only in how they treat ties; top-N per group needs a subquery or CTE.
  • Frames (ROWS, RANGE, GROUPS) choose which neighbouring rows count; LAST_VALUE needs a widened frame.
  • LAG and LEAD read neighbouring rows; WINDOW w AS (...) names a definition you can reuse and extend.
  • SQLite has full window support since 3.25, but no IGNORE NULLS and no window functions in WHERE.

Next: INSERT, UPDATE and DELETE, changing the data you have learned to query.