QUALIFY Clause: SQL Mastery

Introduction

Available in some SQL dialects like Bigquery, Databricks and Snowflake, the QUALIFY clause filters the results of window functions. To use the QUALIFY clause, at least one window function should be present in the SELECT list or the QUALIFY clause.

Syntax 

QUALIFY bool_expression

Bool_expression is any expression that results in a result type boolean. Only rows whose bool_epression evaluates to TRUE are included.

The evaluation of a query with a QUALIFY clause is typically completed in this order. Note, that this order doesn’t always match syntax order.

• FROM

• WHERE

• GROUP BY and aggregation

• HAVING

• WINDOW

• QUALIFY

• DISTINCT

• ORDER BY

• LIMIT

Why use the  QUALIFY clause?

1. Improves code readability

2. Simplicity; instead of using subqueries or CTEs, QUALIFY offers an option to filter out rows directly from a window function output.

3. Post window filtering; Ensures filtering happens after the window function operation is done

4. Efficiency; makes queries run faster by cutting down on unnecessary joins and additional processing steps.

Example

--QUALIFY WITH WINDOW FUNCTION IN THE QUALIFY CLAUSE

SELECT 
    category_id,
    product_id,
    SUM(sales_amount) AS total_sales
FROM 
    sales_data
GROUP BY 
    category_id, 
    product_id
QUALIFY 
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY SUM(sales_amount) DESC) = 1;

--QUALIFY WITH WINDOW FUNCTION IN SELECT LIST

SELECT 
    category_id,
    product_id,
    SUM(sales_amount) AS total_sales,
    ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY SUM(sales_amount) DESC  AS rn
FROM 
    sales_data
GROUP BY 
    category_id, 
    product_id
QUALIFY rn = 1;