Category: Data Query Language (DQL)

Platform Support:

✅ Snowflake | ✅ BigQuery | ❌ Databricks

Description

QUALIFY filters the results of window functions, similar to how HAVING filters aggregate functions. It eliminates the need for a subquery when filtering on window function results. This is particularly useful for ranking, row numbering, and other analytical queries.

Syntax

SELECT columns, window_function() OVER (...)
FROM table

SELECT columns, window_function() OVER (...)
FROM table

SELECT columns, window_function() OVER (...)
FROM table

Platform-Specific Notes

Snowflake:

• Full support for QUALIFY

• Very efficient, recommended over subqueries

• Can reference window functions defined in SELECT or directly in QUALIFY

BigQuery:

• Full support introduced in 2020

• Works identically to Snowflake

• Improves query readability

Databricks:

• ❌ Not supported

• Must use subquery pattern or temporary tables instead

Example 1: Top N per Group (Snowflake/BigQuery)

-- Get top 2 salaries per department
SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM employees
QUALIFY RANK() OVER (PARTITION BY department ORDER BY salary DESC) <= 2

-- Get top 2 salaries per department
SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM employees
QUALIFY RANK() OVER (PARTITION BY department ORDER BY salary DESC) <= 2

-- Get top 2 salaries per department
SELECT 
    department,
    employee_name,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
FROM employees
QUALIFY RANK() OVER (PARTITION BY department ORDER BY salary DESC) <= 2

Databricks Alternative:

-- Databricks: use subquery
SELECT department, employee_name, salary, salary_rank
FROM (
    SELECT 
        department,
        employee_name,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
    FROM employees
)
WHERE salary_rank <= 2

-- Databricks: use subquery
SELECT department, employee_name, salary, salary_rank
FROM (
    SELECT 
        department,
        employee_name,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
    FROM employees
)
WHERE salary_rank <= 2

-- Databricks: use subquery
SELECT department, employee_name, salary, salary_rank
FROM (
    SELECT 
        department,
        employee_name,
        salary,
        RANK() OVER (PARTITION BY department ORDER BY salary DESC) as salary_rank
    FROM employees
)
WHERE salary_rank <= 2

Sample Data (employees table):

Result:

Example 2: Get Most Recent Record per Customer

Snowflake/BigQuery:

SELECT 
    customer_id,
    order_date,
    order_total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1

SELECT 
    customer_id,
    order_date,
    order_total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1

SELECT 
    customer_id,
    order_date,
    order_total,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) as rn
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1

Sample Data (orders table):

Result:

Performance Tips

• Snowflake/BigQuery: QUALIFY is more efficient than subqueries for window function filtering

• Define the window function once in SELECT if you need the ranked column in output

• For Databricks, consider using CTEs for better readability of the subquery approach