--- title: 'Six Practical QUALIFY Patterns' date: 2026-07-23 slug: category-engineering/qualify-patterns cover_url: 'https://static.databend.cn/img/202607/QUALIFYbanner-EN.png' description: '`QUALIFY` does more than remove a layer of nesting. It keeps window-function logic and the filter applied to its result in the same semantic stage, making analytical SQL easier to read, review, and maintain. This article covers six practical patterns, including event deduplication, Top N queries, CTE boundaries, and named windows.' tags: [databend] category: Engineering authors: - coldWater --- **TL;DR:** `QUALIFY` does more than remove a layer of nesting. It keeps window-function logic and the filter applied to its result in the same semantic stage, making analytical SQL easier to read, review, and maintain. This article covers six practical patterns, including event deduplication, Top N queries, CTE boundaries, and named windows. This article assumes familiarity with SQL window functions and common analytical query patterns. {/* truncate */} ## Why QUALIFY Is More Than Less Nesting `QUALIFY` is often introduced as a way to avoid wrapping a window function in a subquery. That is useful, but it is not the main reason to use it. In production queries, the harder problem is preserving intent once window functions, aliases, CTEs, aggregates, and sorting all appear together. `QUALIFY` helps by keeping two related operations in the same query block: - computing a window value; - filtering rows by that value. When these operations are split between an inner query and an outer `WHERE`, readers must move between layers to reconstruct the logic. Keeping them together makes three decisions immediately visible: how rows are partitioned, how they are ordered, and which rows survive. In that sense, `QUALIFY` is not merely syntactic sugar. It improves the reading path of complex analytical SQL. ## 1. Keep Window Calculation and Filtering Together Consider a common requirement: return the most recent login event for every user. Without `QUALIFY`, the query usually computes `row_number()` in a subquery and filters it in the outer query: ```SQL 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`, both decisions stay together: ```SQL 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` is concise, but it can make the filter harder to interpret: ```SQL 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: ```SQL 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`, and `top_payment_rank` often remove the need for an additional comment. 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()` is usually appropriate: ```SQL 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` contain ties and no additional field makes the order deterministic, the query will still choose one of the tied rows, but it may not produce a stable business-level representative. Add a reliable tie-breaker whenever one is available. When the requirement is to preserve ties within the top positions, use `rank()` or `dense_rank()` instead: ```SQL 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` makes the retention rule explicit. Choosing among `row_number()`, `rank()`, and `dense_rank()` is part of defining the expected result, not a formatting detail. ## 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: ```SQL 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` defines the input set for the window calculation, `row_number()` orders rows within each user, and `QUALIFY` retains the latest row. 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` usually adds structure without adding meaning. The following query first computes daily sales by shop, then returns the highest-sales day for each shop: ```SQL 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` possible. ## 6. Reuse Window Rules with Named Windows When several window functions share the same `PARTITION BY` and `ORDER BY`, a named window reduces duplication and makes that shared rule explicit: ```SQL 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`. It should not become a container for unrelated logic. 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`, aggregate filters in `HAVING`, and reserve `QUALIFY` for conditions derived from window functions. 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` is likely the right tool. If the requirement is instead “filter the source rows” or “aggregate first, then filter the groups,” use `WHERE` or `HAVING`. ## Using QUALIFY in Databend Databend supports filtering window-function results directly with `QUALIFY`. The pattern is useful for event analysis, behavioral-data deduplication, Top N rankings, and latest-state extraction. 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` keeps those transformations compact without hiding their semantics behind another query layer. 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` is not that it saves a few lines. It lets a query express one complete idea in one place: compute a window value, then filter by it. 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`, aggregate filters in `HAVING`, and window-result filters in `QUALIFY`.