TopolearnThese features turn awkward multi-step queries (top-N per group, nearest timestamp, wide reports) into one clear statement.
QUALIFY filters on window function results the way HAVING filters on aggregates. The classic top-N-per-group query no longer needs a subquery:
SELECT * FROM sales
QUALIFY row_number() OVER (PARTITION BY region ORDER BY amount DESC) <= 3;ASOF JOIN matches each row to the closest preceding row in another table, which is exactly what time series need: attach the latest known price to each trade, or the latest config to each event. Write ASOF JOIN prices p ON t.symbol = p.symbol AND t.ts >= p.ts. The inequality must be one of >=, >, <=, <, and every left row gets at most one match (use ASOF LEFT JOIN to keep rows with none).
PIVOT turns distinct values of a column into columns: PIVOT sales ON year USING sum(amount) GROUP BY region. DuckDB discovers the values for you, though listing them (ON year IN (2024, 2025)) makes the output schema stable. UNPIVOT does the reverse, folding columns into name/value rows: UNPIVOT wide ON COLUMNS(* EXCLUDE id) INTO NAME month VALUE amount.
All three compile to ordinary operators, so they are fast, and they combine freely with CTEs and window functions.
Example
import duckdb
duckdb.sql("""CREATE TABLE prices AS SELECT * FROM (VALUES
('X', 1, 10), ('X', 5, 11), ('X', 9, 12)) t(sym, ts, price)""")
duckdb.sql("""CREATE TABLE trades AS SELECT * FROM (VALUES
('X', 4, 100), ('X', 7, 50), ('X', 9, 20)) t(sym, ts, qty)""")
print(duckdb.sql("""
SELECT t.ts, t.qty, p.price FROM trades t
ASOF JOIN prices p ON t.sym = p.sym AND t.ts >= p.ts ORDER BY t.ts
""").fetchall()) # [(4, 100, 10), (7, 50, 11), (9, 20, 12)]
print(duckdb.sql("""
SELECT ts, qty FROM trades
QUALIFY row_number() OVER (ORDER BY qty DESC) = 1
""").fetchall()) # [(4, 100)]
print(duckdb.sql("PIVOT trades ON ts IN (4, 7) USING sum(qty) GROUP BY sym").fetchall())
# [('X', 100, 50)]Remember
- QUALIFY filters rows by window function results
- ASOF JOIN attaches the nearest preceding match, ideal for time series
- PIVOT/UNPIVOT reshape between long and wide; list values for a stable schema
Watch out for
- Expecting an ASOF JOIN to return several matches per row; it returns at most the single closest one.