DuckDB ⟿ QUALIFY Clause

The QUALIFY clause in DuckDB is a powerful feature that lets you filter results of window functions directly! Say goodbye to complex nested subqueries when you need to filter based on window function results.

Go to: https://www.quackdb.com/.

Copy the code below and paste it into the editor. And run it.

WITH sales AS (
    SELECT * FROM read_csv_auto('https://raw.githubusercontent.com/creatuluw/datasets/refs/heads/main/global_superstore.csv')
)

SELECT 
    category,
    sub_category,
    SUM(sales) as total_sales,
    RANK() OVER (PARTITION BY category ORDER BY SUM(sales) DESC) as subcategory_rank
FROM sales
GROUP BY category, sub_category
QUALIFY subcategory_rank <= 2;

This query shows the top 2 sub-categories by sales within each category. The QUALIFY clause filters the results after the window function (RANK) is computed, making the code much cleaner than using a subquery!