QUALIFY in Snowflake: Filter Window Functions

1. Introduction

The Window functions calculates an aggregate value over a group of rows and return a single result for each row within that group. However, it is important to note that if you attempt to filter query results based on the window function in the WHERE clause of same query, it will result in an error.

2. Problem with Filtering Query Results of Window Functions

For example, let us consider we want to find the details of employee with highest salary in each department.

The below query will throw an error because filtering on window functions is not supported in the WHERE clause.

SELECT
   Employee_Id,
   Name,
   Department_Id,
   Salary,
   RANK() OVER(PARTITION BY Department_Id ORDER BY Salary DESC) AS SAL_RANK
FROM EMPLOYEE
WHERE SAL_RANK = 1;

The reason for the error is that the internal evaluation of Window Functions takes place only after the evaluation of the WHERE clause.

3. Two-Step Solution for Filtering Query Results of Window Functions

The Window functions are supported in SELECT and ORDER BY clauses. Therefore, one way to filter the results of a query based on a window function is to encapsulate the query within another statement and filter based on the field that uses the window function.

The following query finds the employee with highest salary in each department.

SELECT * FROM(
    SELECT
       Employee_Id,
       Name,
       Department_Id,
       Salary,
       RANK() OVER(PARTITION BY Department_Id ORDER BY Salary DESC) AS SAL_RANK
    FROM EMPLOYEE
)
WHERE SAL_RANK =1;

This is a two-step process to filter the results of a query which uses a window function. To avoid this additional step, Snowflake introduced QUALIFY clause.

4. QUALIFY Clause in Snowflake

QUALIFY clause in a SELECT statement allows you to filter query results of Window functions within the same query. What QUALIFY does with window functions is analogous to what HAVING does with GROUP BY clause.

The QUALIFY clause requires at least one window function to be specified in at least one of the following clauses of the SELECT statement:

• The SELECT column list.
• The filter predicate of the QUALIFY clause.

4.1. Window Function in SELECT Column List

QUALIFY can be used to reference window functions that are in the SELECT column list to filter the records.

The following query uses QUALIFY to reference the window function SAL_RANK to find the employee details with highest salary in each department.

SELECT
    Employee_Id,
    Name,
    Department_Id,
    Salary,
    RANK() OVER(PARTITION BY Department_Id ORDER BY Salary DESC) AS SAL_RANK
FROM EMPLOYEE
QUALIFY SAL_RANK = 1;

4.2. Window Function in Filter Predicate of QUALIFY

The Window functions can be directly specified in the QUALIFY clause to filter records based on their result.

The following query uses Window function in the filter predicate of QUALIFY clause to find the employee details with highest salary in each department.

SELECT
    Employee_Id,
    Name,
    Department_Id,
    Salary
FROM EMPLOYEE
QUALIFY RANK() OVER(PARTITION BY Department_Id ORDER BY Salary DESC) = 1;

5. Conclusion

While it is observed that using QUALIFY does not necessarily guarantee a performance boost as it is evaluated only after a WINDOW function, it simplifies queries that require filtering on the result of window functions. So next time you are working with window functions, don’t forget to explore the QUALIFY clause to simply your queries.

Subscribe to our Newsletter !!

Related Articles: