Snowflake : Filtering the results of window functions in Snowflake [QUALIFY]

One of the features that sets Snowflake apart from other data warehousing solutions is its support for advanced SQL constructs like the QUALIFY clause.

The QUALIFY clause allows you to filter the results of an aggregate query based on the results of another aggregate query. This can be useful in a variety of scenarios, such as identifying the top performers in a sales team or finding the most popular products sold in a particular region.

In this article, we’ll explore the syntax and functionality of the QUALIFY clause in Snowflake, as well as some best practices for using it effectively.

Syntax

The QUALIFY clause is used in conjunction with an aggregate query that uses a GROUP BY clause. Here is the general syntax for a Snowflake query that includes a QUALIFY clause:

SELECT <column1>, <column2>, ..., <aggregate_function>(<expression>) AS <alias>
FROM <table>
GROUP BY <column1>, <column2>, ...
QUALIFY <condition>

In this syntax, the SELECT statement specifies the columns to include in the query, as well as one or more aggregate functions to apply to the data. The GROUP BY clause specifies the columns to group the data by, and the QUALIFY clause filters the results based on a condition that is evaluated after the data has been aggregated.

The condition in the QUALIFY clause can include any valid SQL expression, including comparison operators, logical operators, and aggregate functions. The condition is evaluated after the data has been grouped and aggregated, so it can reference the aliases of the aggregate functions in the SELECT statement.

Example

To demonstrate the functionality of the QUALIFY clause, let’s use an example dataset. We’ll create a table called freshers_in.sales that contains sales data for different products in different regions:

CREATE TABLE freshers_in.sales (
    region VARCHAR(50),
    product VARCHAR(50),
    sales DECIMAL(10,2)
);

INSERT INTO freshers_in.sales VALUES
('North', 'Product A', 1000.00),
('North', 'Product B', 2000.00),
('North', 'Product C', 3000.00),
('South', 'Product A', 1500.00),
('South', 'Product B', 2500.00),
('South', 'Product C', 3500.00),
('East', 'Product A', 2000.00),
('East', 'Product B', 3000.00),
('East', 'Product C', 4000.00),
('West', 'Product A', 1200.00),
('West', 'Product B', 2200.00),
('West', 'Product C', 3200.00);

We can use the following query to calculate the total sales for each region:

SELECT
    region,
    SUM(sales) AS total_sales
FROM
    freshers_in.sales
GROUP BY
    region;

This query will produce the following output:

+--------+-------------+
| REGION | TOTAL_SALES |
+--------+-------------+
| East   |     9000.00 |
| North  |     6000.00 |
| South  |     7500.00 |
| West   |     6600.00 |
+--------+-------------+

Suppose we want to identify the regions with total sales greater than 7000.00. We can use the QUALIFY clause to filter the results based on this condition:

SELECT
    region,
    SUM(sales) AS total_sales
FROM
    freshers_in.sales
GROUP BY
    region
QUALIFY
    total_sales > 7000.00;

This query will produce the following output:

+--------+-------------+
| REGION | TOTAL_SALES |
+--------+-------------+
| East   |     9000.00 |
| South  |     7500.00 |
+--------+-------------+

In this example, we added the QUALIFY clause to the previous query to filter the results based on the total_sales alias. The total_sales alias is calculated by the SUM function in the SELECT statement. The QUALIFY clause then filters the results to only include rows where the total_sales value is greater than 7000.00.

How it works

When you include a QUALIFY clause in a query, Snowflake first evaluates the GROUP BY and aggregate functions in the SELECT statement to produce a set of aggregated data. This aggregated data is then filtered based on the condition specified in the QUALIFY clause.

It’s important to note that the QUALIFY clause is evaluated after the GROUP BY and aggregate functions have been applied. This means that you can use aliases of aggregate functions in the QUALIFY clause, but you cannot use aliases that are defined in the SELECT statement but not part of the aggregate functions.

In addition, the QUALIFY clause only filters the results of the query. It does not affect the way the data is grouped or aggregated. If you want to group or aggregate the data differently based on the filtered results, you will need to use a subquery or another query that builds on the first query.

Best practices

Here are some best practices to keep in mind when using the QUALIFY clause in Snowflake:

1. Use aggregate functions in the SELECT statement: The QUALIFY clause is designed to work with aggregated data, so it’s important to include aggregate functions in the SELECT statement. This ensures that the data is properly grouped and aggregated before the QUALIFY clause is applied.
2. Use aliases for aggregate functions: Using aliases for aggregate functions can make your code more readable and can simplify the use of aliases in the QUALIFY clause.
3. Keep the query simple: The QUALIFY clause can be a powerful tool for filtering data, but it can also make your code more complex. Try to keep the query as simple as possible to make it easier to understand and maintain.
4. Test your queries: As with any SQL query, it’s important to test your code thoroughly to ensure that it produces the expected results. This is especially true when using advanced SQL constructs like the QUALIFY clause.