Bird
Raised Fist0
SnowflakeConceptBeginner · 3 min read

Qualify Clause in Snowflake: Definition and Usage

The QUALIFY clause in Snowflake filters the results of window functions after they are computed. It works like a WHERE clause but applies to the output of window functions, letting you select rows based on ranking or other window calculations.
⚙️

How It Works

The QUALIFY clause lets you filter rows based on the results of window functions, such as ROW_NUMBER(), RANK(), or DENSE_RANK(). Think of it like a filter that happens after Snowflake calculates these special values for each row.

Imagine you have a list of students with their scores, and you want to pick the top scorer in each class. First, Snowflake assigns a rank to each student within their class using a window function. Then, the QUALIFY clause filters the list to keep only the top-ranked students. This is different from WHERE because WHERE filters before window functions run, so it can't use their results.

💻

Example

This example shows how to use QUALIFY to get the highest paid employee in each department.

sql
SELECT department, employee_name, salary,
       ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees
QUALIFY rank = 1;
Output
department | employee_name | salary | rank ------------------------------------------ Sales | Alice | 90000 | 1 IT | Bob | 95000 | 1 HR | Carol | 85000 | 1
🎯

When to Use

Use QUALIFY when you want to filter rows based on window function results without using subqueries or common table expressions. It simplifies queries that find top N records per group, remove duplicates, or rank data.

For example, in sales reports, you might want to show only the top 3 salespeople per region. Instead of writing complex nested queries, QUALIFY lets you write clear and concise SQL.

✅

Key Points

  • QUALIFY filters after window functions are calculated.
  • It works like WHERE but for window function results.
  • Helps simplify queries that rank or filter grouped data.
  • Supported in Snowflake and some other SQL platforms.
✅

Key Takeaways

QUALIFY filters rows based on window function results.
It simplifies queries that select top or ranked rows per group.
QUALIFY runs after window functions, unlike WHERE.
Use it to avoid complex subqueries or joins when filtering ranked data.