WHERE, HAVING, and QUALIFY — filter order
The problem
A report asks for “each customer’s best month, but only among months that actually sold something, and only months whose total is at least 100.” People reach for one WHERE and get the wrong month — or lose a customer — because row filters, group filters, and window filters do not run at the same time.
Official Snowflake documentation gives this evaluation order for a typical SELECT:
FROMWHEREGROUP BYHAVINGWINDOWQUALIFYDISTINCTORDER BYLIMIT
WHERE cannot see an alias of a window function. HAVING cannot see a window result either; it filters groups after aggregation. QUALIFY is the clause that runs after windows.
A working example
WITH sales AS (
SELECT * FROM (VALUES
(1, DATE '2026-01-15', 40),
(1, DATE '2026-01-20', 70),
(1, DATE '2026-02-10', 30),
(1, DATE '2026-02-18', 20),
(2, DATE '2026-01-05', 200),
(2, DATE '2026-03-01', 0),
(2, DATE '2026-03-02', 5)
) t(customer_id, sold_on, amount)
)
SELECT
customer_id,
DATE_TRUNC('month', sold_on) AS month,
SUM(amount) AS month_total
FROM sales
WHERE amount > 0 -- drop zero-amount rows first
GROUP BY customer_id, DATE_TRUNC('month', sold_on)
HAVING SUM(amount) >= 100 -- keep only substantial months
QUALIFY ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY SUM(amount) DESC
) = 1 -- then pick each customer's best remaining month
ORDER BY customer_id;
Executed in DuckDB 2.0.0:
| customer_id | month | month_total |
|---|---|---|
| 1 | 2026-01-01 | 110 |
| 2 | 2026-01-01 | 200 |
Trace:
- Customer 1’s January total is 40+70=110; February is 50 and is removed by
HAVING. - Customer 2’s March would be 5 after the zero-amount row is removed by
WHERE. That month failsHAVING. January (200) remains.
If you had filtered amount >= 100 in WHERE, customer 1 would disappear entirely — neither January line item is 100 on its own. If you skipped HAVING and used only QUALIFY, customer 2’s “best month” could still be a thin March after a mistaken include of the zero row, depending on how you aggregate.
The gotcha
Putting a window filter in WHERE is a syntax error or a logical error, depending on how you write it. Putting a group filter in QUALIFY (or the other way around) often runs and returns a plausible-looking subset that is simply not the subset you meant.
Official docs also note that QUALIFY and HAVING may appear together: HAVING filters after GROUP BY, QUALIFY after window functions. That is a feature, not a reason to pack every predicate into one clause.
LIMIT runs last. LIMIT 10 is not a substitute for “10 random rows” or “the 10 largest groups” unless you have already ordered or sampled. See SAMPLE vs LIMIT.
When not to use this
- A single grain, no aggregation, no window — just use
WHERE. - You only need “groups that pass a threshold” and will not apply a window afterwards —
HAVINGis enough. - The logic is easier to follow as named CTEs (
filtered_rows→monthly→ranked). The clauses still run in the same order; the CTEs just make the order visible.
The example was executed in DuckDB 2.0.0 and returned January 110 for customer 1 and January 200 for customer 2. The clause-order list is from official Snowflake QUALIFY documentation. This was not executed on Snowflake.
Official docs
- QUALIFY (includes the official clause order and a combined
HAVING+QUALIFYnote) - LIMIT / FETCH