Skip to content
Tutorials Center
My learning

Topics

Lesson 26 of 38 ยท Window Functions and Analytics

QUALIFY, Named Windows and Frames

Filter window results with DuckDB's QUALIFY, name and reuse windows with the WINDOW clause, and control ROWS, RANGE and GROUPS frames with EXCLUDE and FILTER.

You know how to compute window functions. This lesson covers the tools that make them tidy and precise: QUALIFY to filter on a window result, the WINDOW clause to name a window, and frame clauses to say exactly which neighbouring rows count.

QUALIFY and a few frame extras are DuckDB features (also found in Snowflake, BigQuery and others). Because the real DuckDB cannot run here, those statements are read-only, each shown beside a runnable portable equivalent.

QUALIFY: WHERE for window functions

WHERE runs before windows are computed, so you cannot filter on a rank. The standard workaround is a subquery. DuckDB adds QUALIFY, which runs after windows:

SQL
-- DuckDB: the best sale per region, no subquery
SELECT region, rep, amount
FROM sales
QUALIFY ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) = 1;

You may also use a select-list alias:

SQL
SELECT region, rep, amount,
       RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rnk
FROM sales
QUALIFY rnk <= 2;

The clause order in DuckDB is FROM, WHERE, GROUP BY, HAVING, WINDOW, QUALIFY, ORDER BY, LIMIT. Portable equivalent, runnable:

SQL QUALIFY equivalent: a subquery filter

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

Another popular QUALIFY pattern is deduplication: keep the newest row per key.

SQL
SELECT *
FROM events
QUALIFY ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY updated_at DESC) = 1;

Named windows

When several functions share a specification, name it once with WINDOW:

SQL One window, many functions

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

A named window can also be extended: OVER (w ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) reuses PARTITION BY and ORDER BY from w and adds a frame. A window can build on another: WINDOW base AS (PARTITION BY region), ordered AS (base ORDER BY day).

Frames: which rows count

The frame sits inside the partition and moves with the current row. It has a unit and two bounds:

SQL
ROWS   BETWEEN 2 PRECEDING AND CURRENT ROW   -- by physical row position
RANGE  BETWEEN INTERVAL 7 DAYS PRECEDING AND CURRENT ROW   -- by value distance
GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING   -- by peer groups of equal ORDER BY values

Bounds are UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING. Key differences:

  • ROWS counts rows. Use it for "the last 3 readings".
  • RANGE uses the ordering value. With a date column, RANGE BETWEEN INTERVAL 6 DAYS PRECEDING AND CURRENT ROW is a true 7-day window even when some days are missing (DuckDB supports interval offsets over dates and timestamps; SQLite, used in the runner, supports numeric offsets only).
  • GROUPS counts groups of tied rows.
SQL ROWS frames: centred and trailing averages

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

The default frame, when the window has an ORDER BY and no frame, is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. Without ORDER BY it is the entire partition. That default is why running totals with ties treat equal values as peers.

Here is RANGE showing that difference on numeric offsets, which SQLite supports:

SQL RANGE by value, not by row count

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

The row at t = 5 has no neighbour within 1 unit, so RANGE sums only its own value, while ROWS still adds the previous row regardless of distance.

EXCLUDE, FILTER and DISTINCT

DuckDB supports more of the standard than most databases:

SQL
-- Average of the other rows in the group (leave out the current row)
AVG(amount) OVER (PARTITION BY region ORDER BY day
                  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
                  EXCLUDE CURRENT ROW)

-- Count only matching rows inside the window
COUNT(*) FILTER (WHERE status = 'error') OVER (ORDER BY ts
                  ROWS BETWEEN 99 PRECEDING AND CURRENT ROW)

-- Distinct values in a window
COUNT(DISTINCT user_id) OVER (PARTITION BY day)

EXCLUDE takes CURRENT ROW, GROUP, TIES or NO OTHERS. FILTER is a neat way to build conditional counts without CASE. DISTINCT aggregates over a frame are supported by DuckDB, though some engines forbid them.

Quick equivalent for the "average of the others" idea, runnable in portable SQL:

SQL Compare each row with the others in its group

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

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does QUALIFY do?

    QUALIFY is to window functions what HAVING is to GROUP BY: it filters on their results.
  2. 2. What is the benefit of the WINDOW clause?

    A named window avoids repeating the same PARTITION BY and ORDER BY in each function.
  3. 3. How does RANGE differ from ROWS?

    RANGE frames are defined by value distance (and include peers), ROWS frames by row position.
  4. 4. Which frame is used by default when OVER has ORDER BY but no frame?

    The default is a cumulative range up to the current row and its peers.

Summary

  • QUALIFY filters on window results; subqueries are the portable equivalent.
  • WINDOW name AS (...) shares and extends window definitions.
  • ROWS counts rows, RANGE measures value distance, GROUPS counts peer groups.
  • EXCLUDE, FILTER and DISTINCT give DuckDB extra precision inside windows.

Next: Time Series: Gaps, Buckets and ASOF Joins