KATER__WINDOW_AGGREGATION

Family: Windows

Syntax

1
KATER__WINDOW_AGGREGATION(field, function, frame [, size]) OVER ([PARTITION BY ...] ORDER BY ...)

Arguments

Required

Optional

Returns

A number. The field menu shows it as the aggregate it takes: a count as a whole number, and the others in the format of the field it reads.

Usage notes

• Write OVER (ORDER BY ...), and add PARTITION BY to start again for each group. It writes its own frame, so its OVER takes no ROWS or RANGE.
• A running frame compiles to ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, and a trailing frame of size 3 to ROWS BETWEEN 2 PRECEDING AND CURRENT ROW.
• It counts rows, not calendar periods, so a month with no row is not in the frame.
• A running sum of a measure that cannot be added up across a dimension, such as Revenue across Currency, must start over for each value. Put that dimension in PARTITION BY.

Example

This Sandbox query reads the sample shop’s orders. The table shows the rows that Kater gave.

1
SELECT
2
  orders.order_month AS "Order Month",
3
  EVAL(orders.sum__quantity) AS "Units",
4
  CUSTOM_CALCULATION(
5
    'window_aggregation__sum__quantity',
6
    KATER__WINDOW_AGGREGATION(orders.sum__quantity, 'sum', 'running') OVER (ORDER BY orders.order_month)
7
  ) AS "Units So Far"
8
FROM kater_dataset.orders AS orders
9
WHERE
10
  KATER__IS_ON_OR_AFTER(orders.order_date, DATE '2026-05-01')
11
GROUP BY
12
  orders.order_month
13
ORDER BY
14
  1

Compiles to

The call compiles to the same SQL on DuckDB and on Snowflake.

1
SUM(orders.sum__quantity) OVER (ORDER BY orders.order_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)

See also

• Running totals, ranks and windows
• Other functions in Windows: KATER__LAG, KATER__LEAD, KATER__FIRST_VALUE, KATER__LAST_VALUE, KATER__DELTA, KATER__PERCENT_DELTA, KATER__RANK, KATER__ROW_NUMBER, KATER__SHARE_OF_TOTAL