Snowflake SQL Patterns

WHERE, HAVING, and QUALIFY — filter order

Why a filter in the wrong clause silently drops the wrong rows, using official clause 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:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. WINDOW
  6. QUALIFY
  7. DISTINCT
  8. ORDER BY
  9. LIMIT

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:

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

Verification

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