Aggregate Functions, Window Functions and QUALIFY in Snowflake SQL

1.Aggregate functions: many rows in, one row out

An aggregate function reads many input rows and returns one output value. A scalar function such as COS returns one value for each input row. Run SUM(x) over a three-row table and you get one row. Run COS(x) over the same table and you get three. One detail is easy to miss: an aggregate always returns exactly one row, even when there are no input rows. Usually that row contains NULL, but some functions return 0 or an empty string instead.

Snowflake's list of aggregates goes well beyond SUM, AVG and COUNT. Several groups are directly useful in transformation work. The estimation families (HyperLogLog, t-Digest) return approximations rather than exact values: for example, HLL returns an approximation of COUNT(DISTINCT ...), so choose them only where an estimate is acceptable.

NULL handling is the area exam questions most often test. Some aggregates ignore NULLs: AVG over 1, 5 and NULL gives 3. If every input value is NULL, the result is NULL. When you pass more than one column, as in COUNT(col1, col2), a row is ignored if any one of those columns is NULL. Expressions behave the same way. In SUM(x + y), a NULL in either column makes the expression NULL, so that row is skipped. GROUP BY is different: it keeps rows where some columns are NULL and gives them their own groups. The test table below has four rows, but only one has no NULLs:

SELECT COUNT(x, y) FROM test_null_aggregate_functions;

+-------------+
| COUNT(X, Y) |
|-------------|
|           1 |
+-------------+

Sources12

2.Window functions: one result per row, computed over a partition

A window function computes over a set of related rows, but it does not collapse them. It still returns one row for every input row. Many aggregates, including SUM, COUNT and AVG, have window versions with the same name. What turns one into a window function is the OVER clause. OVER has three optional parts: PARTITION BY, which divides rows into groups; ORDER BY, which sorts rows within each partition; and a window frame, which picks the rows around the current row. An empty OVER() is valid. It treats all rows as a single partition.

The regular AVG(menu_cogs_usd) ... GROUP BY 1 query returns one row per category. The window version below returns the category average on every one of the 60 rows, and the calculation starts again when the category changes:

SELECT menu_category,
    AVG(menu_cogs_usd) OVER(PARTITION BY menu_category) avg_cogs
  FROM menu_items
  ORDER BY menu_category
  LIMIT 15;

With ORDER BY and a frame added, the same function becomes a moving average. Two kinds of window function need ORDER BY. The first is any function with an explicit frame, such as a running total or a moving average, because "preceding" and "following" mean nothing until the rows are sorted. The second is ranking functions such as RANK, DENSE_RANK and NTILE. The ORDER BY inside OVER only controls the order in which the function processes rows. It does not sort the query's output, so you often need both. Three more rules apply. Make the ordering deterministic by adding tiebreaker columns. Do not use ordinal positions inside OVER, because ORDER BY 2 there means the constant 2. And with some functions, ORDER BY implies a default frame, which is why Snowflake recommends always declaring the frame explicitly.

Ranking works the same way. The query below ranks salespeople by total sales: the ORDER BY inside OVER sets the ranking order, and there is no PARTITION BY, so all rows form one partition. Jones (1000) gets rank 1 and Smith (600) gets rank 4. Add a separate ORDER BY to the query itself if you want the output displayed by rank.

SELECT
    salesperson_name,
    sales_in_dollars,
    RANK() OVER (ORDER BY sales_in_dollars DESC) AS sales_rank
  FROM sales_table;

SELECT menu_category, menu_price_usd, menu_cogs_usd,
    AVG(menu_cogs_usd) OVER(PARTITION BY menu_category
      ORDER BY menu_price_usd, menu_cogs_usd  ?  BETWEEN CURRENT ROW and 2 FOLLOWING) avg_cogs
  FROM menu_items
  ORDER BY menu_category, menu_price_usd, menu_cogs_usd
  LIMIT 15;

Sources34

3.Shaping SQL for simpler, leaner transformations

Snowflake gives you several ways to remove extra layers from transformation SQL. The first is QUALIFY, which filters on window function results. It does for window functions what HAVING does for aggregates and GROUP BY. Clauses in a SELECT normally run in this order: FROM, WHERE, GROUP BY, HAVING, WINDOW, QUALIFY, DISTINCT, ORDER BY, LIMIT. WHERE runs too early to see a window result. QUALIFY runs after the window step, so QUALIFY ROW_NUMBER() OVER (...) = 1 replaces the wrapping subquery. QUALIFY needs at least one window function, either in the SELECT list or in the QUALIFY predicate itself.

The second is the WITH clause. It comes before the body of a SELECT and defines one or more common table expressions (CTEs). Each CTE is a named query that later parts of the statement can refer to, for example in the FROM clause. If a report needs the same aggregation in three places, you can write it once as a CTE and refer to it by name, instead of copying the logic into three subqueries. WITH RECURSIVE handles hierarchies by joining an anchor clause and a recursive clause with UNION ALL.

The third applies to array data. Higher-order functions (FILTER, REDUCE, TRANSFORM) change array elements inside the row. The documentation names avoiding LATERAL FLATTEN and one-off UDFs as a benefit, so you have fewer objects to create, maintain and grant access to.

Beyond simplifying syntax, the documentation points to concrete ways to make SQL leaner. The guidance below comes from Snowflake's dynamic-table optimization page, but the underlying ideas apply to transformation queries generally:

- Filter early. Apply WHERE clauses as close to the base tables as possible, so later steps process fewer rows. - Watch blocking operators. Window functions, GROUP BY and DISTINCT must see all input rows before producing output. When one query combines several of them, each waits for the previous one, which reduces parallelism and increases memory pressure. Splitting a pipeline so each stage does one blocking step is the recommended remedy. - Remove redundant DISTINCT. Where data is already unique, or you deduplicate upstream, drop DISTINCT. QUALIFY with ROW_NUMBER = 1 is also explicit about which duplicate to keep. - Improve data locality. When rows with matching keys sit in fewer micro-partitions, less data is scanned. The page recommends clustering base tables by the JOIN, GROUP BY or PARTITION BY keys, and including PARTITION BY in window functions so the whole dataset is not treated as one partition.

The general query guide also lists techniques that improve performance: eliminating redundant joins (joins on a key column that refer to tables not needed), top-K pruning for SELECT statements with LIMIT and ORDER BY, reusing persisted query results, and reading query profiles and query insights to find what to fix. The search optimization service is also recommended for querying VARIANT data. Estimation functions such as HLL and APPROX_PERCENTILE trade exactness for an estimate, so use them only where an approximation is acceptable.

Sources5678

4.Reading VARIANT paths: case and quoting

Semi-structured data stored in a VARIANT column is read with a path: a colon after the column name, then dot notation (src:salesperson.name) or bracket notation (src['salesperson']['name']). Two rules matter for exam questions. First, the column name is case-insensitive but element names are case-sensitive, so SRC:salesperson.name matches src:salesperson.name but SRC:Salesperson.Name does not. Second, JSON keys follow different rules from SQL identifiers: if an element name does not conform to identifier rules, for example because it contains a space, enclose it in double quotes.

Sources6

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.COUNT(x, y) counts every row where at least one of the two columns has a value.Why is that wrong?

   When an aggregate is given several columns, it skips any row where even one of them is NULL. Only rows with every column filled in are counted.

   Covered in Aggregate functions: many rows in, one row out

2. 2.OVER (PARTITION BY 1 ORDER BY 2) partitions by the first column and sorts by the second, just like a query-level ORDER BY 2.Why is that wrong?

   Ordinal positions are not supported inside a window's ORDER BY. There, the number is read as a constant.

   Covered in Window functions: one result per row, computed over a partition

3. 3.You can filter on a ROW_NUMBER() result in WHERE, or with HAVING, in the same SELECT.Why is that wrong?

   WHERE and HAVING both run before window functions are computed. QUALIFY is the clause that filters on window results in the same SELECT.

   Covered in Shaping SQL for simpler, leaner transformations