Snowflake
Filter Window Function Results

Snowflake QUALIFY: How to Filter Window Function Results

The QUALIFY clause in Snowflake is used to filter the results of window functions, which are functions that perform calculations across a set of table rows that are related to the current row. This is similar to the HAVING clause that filters aggregate functions (GROUP BY), but QUALIFY does this specifically for window functions without needing nested queries.

Syntax


_10
SELECT <column_list>
_10
FROM <data_source>
_10
[GROUP BY ...]
_10
[HAVING ...]
_10
QUALIFY <predicate>
_10
[ ... ]

Why use the QUALIFY clause?

The QUALIFY clause simplifies SQL queries by eliminating the need for nested subqueries when filtering results based on window functions. Below are two examples that demonstrate how QUALIFY can simplify the same query that traditionally requires a nested subquery.

Scenario: Identifying top sales days

Suppose you want to identify the top three sales days for each store based on total sales.

Traditional nested query approach

Traditionally, you might use a nested query with a window function and an outer query that filters these results:


_10
SELECT *
_10
FROM (
_10
SELECT store_id, sales_date, total_sales,
_10
ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY total_sales DESC) AS rank
_10
FROM store_sales
_10
) AS subquery
_10
WHERE rank <= 3;

Simplified query using QUALIFY

With QUALIFY, you can incorporate this filtering directly in the main query, which simplifies the overall approach:


_10
SELECT store_id, sales_date, total_sales
_10
FROM store_sales
_10
QUALIFY ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY total_sales DESC) <= 3;

❄️ In Snowflake, expressions in the SELECT clause, such as window functions, can be referenced by their column aliases in other clauses.


_10
SELECT store_id, sales_date, total_sale
_10
ROW_NUMBER() OVER (PARTITION BY store_id ORDER BY total_sales DESC) as rank
_10
FROM store_sales
_10
QUALIFY rank <= 3;

Examples

Filter on the last known location

Let's find the last known location of each character from the "The Hitchhiker's Guide to the Galaxy" before the destruction of Earth, assuming we have a timeline of events for each character leading up to that moment.

Input

Table: galactic_travel_log

idcharacterplanetvisit_order
1Zaphod BeeblebroxBetelgeuse Five1
2Zaphod BeeblebroxEarth2
3TrillianEarth1
4Arthur DentEarth1
5Ford PrefectBetelgeuse Seven1
6Ford PrefectEarth2

Snowflake SQL Query


_12
SELECT
_12
id,
_12
character,
_12
planet,
_12
visit_order
_12
FROM
_12
galactic_travel_log QUALIFY ROW_NUMBER() OVER (
_12
PARTITION BY
_12
character
_12
ORDER BY
_12
visit_order DESC
_12
) = 1;

Output

idcharacterplanetvisit_order
2Zaphod BeeblebroxEarth2
3TrillianEarth1
4Arthur DentEarth1
6Ford PrefectEarth2
Y42 SQL completion user interface

Your AI copilot for Snowflake SQL

Write production-ready SQL with AI-based suggestions.

Start for free