The Problem: Filtering Window Results Is Verbose

You need to find the top-selling product in each category. You write a query with ROW_NUMBER():

SELECT category, product, sales
FROM (
    SELECT 
        category,
        product,
        sales,
        ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
    FROM products
) t
WHERE rn = 1;

It works. But it’s three levels deep for a simple operation. Every time you need to filter on a window result — top-N per group, moving averages above a threshold, ranking-based deduplication — you repeat this subquery pattern.

And when your window function gets complex (RANK(), LAG(), custom frames), the subquery becomes unreadable.

The One-Trick Solution: QUALIFY

DuckDB supports the QUALIFY clause — a SQL-standard feature that lets you filter on window function results directly in the main query. No subquery needed.

SELECT category, product, sales
FROM products
QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) = 1;

One query replaces three. That’s the power of QUALIFY.

More Real-World Examples

Example 1: Top-N Per Group

-- ❌ Old way: subquery
SELECT category, product, sales
FROM (
    SELECT category, product, sales,
           ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
    FROM products
) t
WHERE rn <= 3;

-- ✅ New way: QUALIFY
SELECT category, product, sales
FROM products
QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) <= 3;

Example 2: Filtering on LAG() Results

-- Find days where sales dropped more than 20% compared to yesterday
SELECT date, sales, LAG(sales) OVER (ORDER BY date) AS prev_sales
FROM daily_sales
QUALIFY LAG(sales) OVER (ORDER BY date) IS NOT NULL
    AND (sales - LAG(sales) OVER (ORDER BY date)) / LAG(sales) OVER (ORDER BY date) < -0.2;

Without QUALIFY, you’d need a subquery to compute LAG() first, then filter on it. With QUALIFY, you write the window function once and filter on it directly.

Example 3: Running Average Above Threshold

-- Find dates where the 7-day moving average exceeds 1000
SELECT date, sales, AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma7
FROM daily_sales
QUALIFY AVG(sales) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) > 1000;

The window function is defined once in both SELECT and QUALIFY — DuckDB’s optimizer reuses the computation.

Example 4: RANK-Based Deduplication

-- Keep only the first occurrence of each product (by earliest order date)
SELECT order_id, product, order_date, amount
FROM orders
QUALIFY RANK() OVER (PARTITION BY product ORDER BY order_date) = 1;

How QUALIFY Works Under the Hood

The SQL execution order is:

FROM → WHERE → GROUP BY → HAVING → WINDOW FUNCTIONS → QUALIFY → SELECT → ORDER BY → LIMIT

QUALIFY runs after window functions are computed but before the final SELECT projection. This means:

1. Window functions are computed normally
2. QUALIFY filters rows based on those computed values
3. The SELECT list can still reference the same window functions (DuckDB reuses them)

This is why you don’t need a subquery — DuckDB computes the window once and applies the filter in the same pass.

Performance Comparison

In practice, QUALIFY and subquery approaches produce the same execution plan in DuckDB — the optimizer is smart enough to recognize both patterns. The difference is purely in code clarity.

For a 10M-row product table:

• Subquery approach: ~850ms
• QUALIFY approach: ~850ms (identical plan)
• Code reduction: from 8 lines to 3 lines

Edge Cases and Gotchas

1. QUALIFY Can Reference SELECT Aliases

SELECT category, product, sales,
       ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
FROM products
QUALIFY rn <= 3;

This works because QUALIFY sees the computed column aliases from SELECT.

2. Multiple Window Functions in QUALIFY

SELECT category, product, sales,
       ROW_NUMBER() OVER w AS rn,
       LAG(sales) OVER w AS prev_sales
FROM products
WINDOW w AS (PARTITION BY category ORDER BY sales DESC)
QUALIFY rn = 1 AND prev_sales IS NOT NULL;

Define your window once with WINDOW clause, reference it everywhere.

3. QUALIFY + ORDER BY Interaction

SELECT category, product, sales
FROM products
QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) = 1
ORDER BY category;

QUALIFY filters first, then ORDER BY sorts the remaining rows. This is the correct order.

When NOT to Use QUALIFY

QUALIFY is supported in DuckDB but may not be available in all databases. If you need cross-database compatibility:

• PostgreSQL 13+: Supports QUALIFY
• BigQuery: Supports QUALIFY
• Snowflake: Does NOT support QUALIFY (use subquery instead)
• MySQL: Does NOT support QUALIFY

If your code needs to run on multiple platforms, stick to subqueries. But for DuckDB-only pipelines, QUALIFY is the cleanest option.

Summary

The one trick: whenever you find yourself wrapping a window function in a subquery just to filter on it, replace the subquery with QUALIFY. Your future self will thank you.

Subscribe to DuckDB Lab for weekly practical tips that you can use immediately.