Analytical SQL Tips Series —Qualify Clause

In SQL, the QUALIFY clause is essential for filtering results from WINDOW functions, serving a similar purpose as the HAVING clause does with GROUP BY aggregates. This specialized clause eliminates the need for subqueries or WITH clauses(CTEs), streamlining complex queries directly within the main SQL statement.

The necessity of the QUALIFY clause arises from the inherent limitations in the SQL execution order. Specifically, SQL processes the WHERE clause before it computes GROUP BY aggregations and WINDOW functions. This sequence means that results from WINDOW functions cannot be directly filtered using the WHERE clause, as it executes before the WINDOW functions even generate their outputs.

Traditionally, this limitation might force the use of a subquery or a WITH clause to first calculate and then filter the WINDOW function results. However, the QUALIFY clause simplifies this by allowing direct filtering of the results immediately after their generation, significantly enhancing both query performance(depends on the query optimizer) and readability.

Try it out in our demo

Try it on DuckDB WASM . It takes about 10 seconds to initialize DuckDB and create the table

CREATE TABLE Weather AS 
FROM 'https://recce-dbt-tips.s3.amazonaws.com/central-park-weather.csv';

SELECT
    date,
    tmax,
    AVG(tmax) OVER (PARTITION BY EXTRACT(MONTH FROM date)) AS monthly_avg_temp
FROM
    Weather
QUALIFY
    tmax > monthly_avg_temp * 1.1
ORDER BY
    date;

SELECT
    date,
    tmax,
    monthly_avg_temp
FROM
    (
        SELECT
            date,
            tmax,
            AVG(tmax) OVER (PARTITION BY EXTRACT(MONTH FROM date)) AS monthly_avg_temp
        FROM
            Weather
    ) AS subquery
WHERE
    tmax > monthly_avg_temp * 1.1
ORDER BY
    date;

You can apply these SQL tip in your dbt code review. For more about analytical SQL tips, follow Recce on Linkedin.