Skip to content
Tutorials Center
My learning

Topics

Lesson 24 of 38 ยท Window Functions and Analytics

Window Functions: The Basics

Understand window functions in DuckDB: OVER, PARTITION BY and ORDER BY, how a window differs from GROUP BY, and the aggregate functions you can run over a window.

An aggregate with GROUP BY squeezes many rows into one. A window function does the calculation across a set of related rows but keeps every row. That lets you put a group total, a running sum or a rank next to the original data, which is the heart of analytics work. DuckDB has one of the most complete window implementations around and runs it in parallel on large data.

Window syntax is standard SQL, so every example here is portable and runs in your browser. DuckDB-only extras (like QUALIFY) come in the next lessons.

The shape of a window

SQL
function(args) OVER (
  PARTITION BY col1, col2     -- split rows into independent groups
  ORDER BY col3               -- order inside each group
  frame_clause                -- which rows around the current one to use
)

All three parts are optional. OVER () with nothing means "the whole result is one window". Let's use a small sales table:

SQL A group total beside every row

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

Every sale appears with its region total and the grand total. With GROUP BY you would need a join back to the detail rows to get the same picture.

PARTITION BY versus GROUP BY

GROUP BY window with PARTITION BY
Rows returned one per group all input rows
Can mix detail and totals no (without a join) yes
Can be used in WHERE HAVING filters groups not directly; wrap in a subquery or use QUALIFY

Windows are computed after WHERE, GROUP BY and HAVING, and before ORDER BY and LIMIT. You may therefore use window functions only in the SELECT list and ORDER BY (and QUALIFY in DuckDB), never in WHERE.

flowchart LR
    A["FROM / JOIN"] --> B["WHERE"] --> C["GROUP BY / HAVING"] --> D["WINDOW functions"] --> E["QUALIFY"] --> F["ORDER BY / LIMIT"]

Where window functions are evaluated in a query.

Share of the total

A classic use is percentage of a group:

SQL Share of each sale within its region

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

You can reuse the same window several times; DuckDB computes shared windows once. The next lesson shows how to name a window so you do not repeat it.

What you can run over a window

  • Aggregates: SUM, AVG, COUNT, MIN, MAX, and in DuckDB also STDDEV, MEDIAN, QUANTILE_CONT, LIST, STRING_AGG, ARG_MAX, ARG_MIN and more.
  • Ranking: ROW_NUMBER, RANK, DENSE_RANK, PERCENT_RANK, CUME_DIST, NTILE (see ranking and running totals).
  • Offsets: LAG, LEAD, FIRST_VALUE, LAST_VALUE, NTH_VALUE.

Each family behaves differently with ORDER BY inside the window, so let us look at that now.

ORDER BY inside OVER

Adding ORDER BY to an aggregate window turns it into a running calculation, because the default frame becomes "from the first row of the partition up to the current row":

SQL A running total per region

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

Careful with one subtlety: with ORDER BY and the default frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, rows that tie on the ordering value are peers and all receive the same running total. Add a unique tie-breaker (such as id) when you want a strict row-by-row total. Frames are explained in QUALIFY, named windows and frames.

NULLs and empty windows

Aggregates ignore NULL inputs, as usual, so AVG over a window averages only the non-null values. A window with no non-null values returns NULL (for COUNT, 0). Ordering places NULLs according to NULLS FIRST or NULLS LAST; DuckDB's default puts them last in ascending order, and you can state it explicitly: ORDER BY ts DESC NULLS LAST.

Performance note

DuckDB partitions and sorts the data in parallel, and avoids resorting when several windows share the same PARTITION BY and ORDER BY. Writing windows that share the same specification (or one named window) is not only shorter, it can be faster. Still, windows on tens of millions of rows with many distinct partitions use memory; check profiling if a query surprises you.

Common mistakes

  • Using a window in WHERE: wrap the query in a subquery or use QUALIFY.
  • Forgetting PARTITION BY and getting a global result.
  • Expecting a strict running total with duplicate order values (peers share a result).
  • Confusing COUNT(*) OVER () with COUNT(*); the first returns the total on every row.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How does a window function differ from GROUP BY?

    Windows add a calculated column to each row instead of collapsing rows.
  2. 2. What does SUM(x) OVER () return?

    An empty OVER () makes the entire result one window.
  3. 3. Where can window functions not appear?

    WHERE runs before windows are computed. Use a subquery or QUALIFY to filter on a window result.
  4. 4. What happens to SUM(...) OVER (ORDER BY ...) with the default frame?

    With ORDER BY the default frame ends at the current row (and its peers), giving a cumulative result.

Summary

  • function() OVER (PARTITION BY ... ORDER BY ...) computes across rows without collapsing them.
  • Windows run after WHERE/GROUP BY/HAVING; filter on them with a subquery or QUALIFY.
  • ORDER BY inside the window gives running calculations; ties are peers.
  • Aggregates, ranking and offset functions all use the same OVER clause.

Next: Ranking, Offsets and Running Totals