Advanced SQL Features in DuckDB

DuckDB implements the full standard SQL analytical toolkit — window functions, CTEs, recursive queries — plus several quality-of-life extensions that remove boilerplate common in other engines.

Window Functions & QUALIFY

SELECT
    customer, order_date, amount,
    RANK() OVER (PARTITION BY customer ORDER BY amount DESC) AS rank_by_spend
FROM orders
QUALIFY rank_by_spend = 1;   -- top order per customer, no subquery needed

QUALIFY filters on window function results directly, avoiding the classic SELECT * FROM (SELECT ..., RANK() OVER (...) AS r FROM t) WHERE r = 1 wrapper query you’d need in plain ANSI SQL.

Common Table Expressions

WITH monthly_totals AS (
    SELECT date_trunc('month', order_date) AS month, SUM(amount) AS total
    FROM orders
    GROUP BY ALL
)
SELECT month, total, total - LAG(total) OVER (ORDER BY month) AS mom_change
FROM monthly_totals
ORDER BY month;

Recursive CTEs (WITH RECURSIVE) are also supported, for hierarchical/graph-style queries.

PIVOT & UNPIVOT

PIVOT orders
ON status
USING SUM(amount)
GROUP BY customer;

PIVOT/UNPIVOT are native SQL statements in DuckDB (not a function you call inside SELECT), which makes reshaping wide/long data far less verbose than a hand-rolled CASE WHEN per column.

ASOF Joins

An ASOF join matches each row to the closest earlier (or later) row in another table by a time/ordering column — the classic “find the most recent price at or before this trade timestamp” problem in finance and IoT data:

SELECT t.trade_id, t.trade_time, p.price
FROM trades t
ASOF JOIN prices p
  ON t.symbol = p.symbol AND t.trade_time >= p.price_time;

Without a native ASOF JOIN, this requires a correlated subquery or a window function trick in most other databases.

Semi-Structured Data: JSON & Nested Types

INSTALL json; LOAD json;

SELECT
    data->>'$.user.name'                    AS user_name,
    json_extract(data, '$.items[0].sku')    AS first_sku
FROM raw_events;

-- Or query a .jsonl / .json file directly, no loading step
SELECT * FROM read_json_auto('events.jsonl');

Combined with LIST/STRUCT columns from the previous guide, DuckDB lets you flatten and reshape semi-structured data with plain SQL instead of a separate processing step.

Sampling

SELECT * FROM large_table USING SAMPLE 1%;         -- fast approximate sample
SELECT * FROM large_table USING SAMPLE 10000 ROWS; -- fixed row count

Useful for quickly prototyping a query against a huge dataset before running it for real.