Six Practical QUALIFY Patterns

TL;DR:

QUALIFY

This article assumes familiarity with SQL window functions and common analytical query patterns.

Why QUALIFY Is More Than Less Nesting

QUALIFY

In production queries, the harder problem is preserving intent once window functions, aliases, CTEs, aggregates, and sorting all appear together.

QUALIFY

• computing a window value;

• filtering rows by that value.

When these operations are split between an inner query and an outer

WHERE

In that sense,

QUALIFY

1. Keep Window Calculation and Filtering Together

Consider a common requirement: return the most recent login event for every user.

Without

QUALIFY

row_number()

SELECT *
FROM (
    SELECT
        user_id,
        event_time,
        row_number() OVER (
            PARTITION BY user_id
            ORDER BY event_time DESC
        ) AS rn
    FROM events
    WHERE event_type = 'login'
) t
WHERE rn = 1;

The query is valid, but its intent is split across two levels. The inner query defines the ranking, while the outer query decides which rank to retain.

With

QUALIFY

SELECT
    user_id,
    event_time,
    row_number() OVER (
        PARTITION BY user_id
        ORDER BY event_time DESC
    ) AS rn
FROM events
WHERE event_type = 'login'
QUALIFY rn = 1;

The query now reads in the same order as the requirement:

1. Keep login events.

2. Rank each user's events from newest to oldest.

3. Retain the first row in each partition.

The ranking rule and the selection rule are visible in one place.

2. Name Window Results to Expose Intent

Window expressions can become noisy once partitioning and tie-breaking rules are added. Placing the entire expression inside

QUALIFY

SELECT
    user_id,
    session_id,
    amount
FROM payments
QUALIFY row_number() OVER (
    PARTITION BY user_id
    ORDER BY amount DESC, session_id
) = 1;

A descriptive alias separates the window definition from its business meaning:

SELECT
    user_id,
    session_id,
    amount,
    row_number() OVER (
        PARTITION BY user_id
        ORDER BY amount DESC, session_id
    ) AS top_payment_rank
FROM payments
QUALIFY top_payment_rank = 1;

The alias is not just a shorthand for reuse. It explains what the derived value represents. Names such as

latest_row

department_rank

dedup_rank

top_payment_rank

Aliases are especially useful when the window specification is long, the result is referenced more than once, or the query should communicate its business rule at a glance.

3. Make Top 1 and Top N Semantics Explicit

Many ranking bugs come from choosing the wrong window function rather than from the filter itself.

When the requirement is to keep exactly one row per group,

row_number()

SELECT
    user_id,
    event_time,
    row_number() OVER (
        PARTITION BY user_id
        ORDER BY event_time DESC, event_id DESC
    ) AS latest_row
FROM events
QUALIFY latest_row = 1;

This means “return one row according to the current ordering rule.” If the columns in

ORDER BY

When the requirement is to preserve ties within the top positions, use

rank()

dense_rank()

SELECT
    department,
    employee_id,
    salary,
    rank() OVER (
        PARTITION BY department
        ORDER BY salary DESC
    ) AS salary_rank
FROM employees
QUALIFY salary_rank <= 3;

Here, the window function determines how ties are handled, while

QUALIFY

row_number()

rank()

dense_rank()

4. Give WHERE, HAVING, and QUALIFY Separate Jobs

Complex queries become difficult to reason about when filters from different semantic stages are mixed together. A useful division of responsibility is:

• WHERE
  

  filters source rows before aggregation or window evaluation;

• HAVING
  

  filters aggregate results;

• QUALIFY
  

  filters window-function results.

For example:

SELECT
    user_id,
    event_time,
    row_number() OVER (
        PARTITION BY user_id
        ORDER BY event_time DESC
    ) AS rn
FROM events
WHERE event_type = 'login'
QUALIFY rn = 1;

Each clause has one clear role.

WHERE

row_number()

QUALIFY

If a source-level condition is postponed until after the window calculation, readers must stop and work out whether the ranking applies to all events or only to the relevant subset. Keeping each filter in its proper stage removes that ambiguity.

5. Use CTEs for Semantic Stages, Not Window Filtering

CTEs remain valuable in complex SQL, but each one should represent a meaningful transformation. A good boundary might prepare source data, normalize fields, or perform a pre-aggregation. A CTE that exists only to expose a window alias to an outer

WHERE

The following query first computes daily sales by shop, then returns the highest-sales day for each shop:

WITH daily_sales AS (
    SELECT
        shop_id,
        sale_date,
        sum(amount) AS daily_amount
    FROM sales
    GROUP BY shop_id, sale_date
)
SELECT
    shop_id,
    sale_date,
    daily_amount,
    row_number() OVER (
        PARTITION BY shop_id
        ORDER BY daily_amount DESC, sale_date DESC
    ) AS sales_rank
FROM daily_sales
QUALIFY sales_rank = 1;

The CTE has a clear responsibility: it changes the data grain to one row per shop per day. The outer query performs the window calculation and filters the result in the same stage. This is different from adding a subquery solely to make

WHERE rn = 1

6. Reuse Window Rules with Named Windows

When several window functions share the same

PARTITION BY

ORDER BY

SELECT
    user_id,
    event_time,
    row_number() OVER w AS rn,
    lag(event_time) OVER w AS prev_event_time
FROM events
WINDOW w AS (
    PARTITION BY user_id
    ORDER BY event_time DESC
)
QUALIFY rn = 1;

This has three practical benefits:

• reviewers can see immediately that both functions use the same window;

• the window definition needs to be checked only once;

• QUALIFY rn = 1
  

  reads as a direct use of that shared rule.

Named windows become increasingly useful as the number of window functions grows.

When QUALIFY Is the Wrong Tool

Flattening a query does not mean putting every condition into

QUALIFY

The following usually belong elsewhere:

• source-level business filters;

• multi-stage aggregation logic;

• long, repeated window expressions;

• ordinary Boolean conditions unrelated to window results.

A useful rule is to keep basic filters in

WHERE

HAVING

QUALIFY

You can test the fit by describing the query in plain language. If the requirement sounds like “rank rows within each group, then keep the first one,” “calculate a rank, then keep the top three,” or “compute a window value, then filter by it,”

QUALIFY

If the requirement is instead “filter the source rows” or “aggregate first, then filter the groups,” use

WHERE

HAVING

Using QUALIFY in Databend

Databend supports filtering window-function results directly with

QUALIFY

For data engineers familiar with Snowflake or BigQuery syntax, this support reduces migration friction. For analytics engineers, it removes nesting that exists only to filter a window result and keeps the query closer to the business requirement.

These patterns also appear frequently in semi-structured event data and AI agent traces: rank and deduplicate events, select the latest state, identify important steps, or retain the highest-scoring paths.

QUALIFY

This reflects a broader goal in Databend: provide familiar, composable SQL features that make analytical workloads both efficient to run and practical for teams to maintain.

Conclusion

The strongest reason to use

QUALIFY

For latest-row selection, Top N queries, deduplication, ranking filters, and state extraction, this often produces SQL that is flatter, easier to review, and easier to maintain. The boundary still matters: keep source filters in

WHERE

HAVING

QUALIFY