KATER__DELTA

Family: Windows

Syntax

1
KATER__DELTA(field) OVER ([PARTITION BY ...] ORDER BY ...)

Arguments

Returns

A number. NULL on the first row of each partition. The field menu writes the @format of the field it reads beside it, or @format number(2) where that field has none and can have decimals.

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.
• It counts rows, not calendar periods, so a month with no row is skipped.

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
    'delta__sum__quantity',
6
    KATER__DELTA(orders.sum__quantity) OVER (ORDER BY orders.order_month)
7
  ) AS "Change"
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
orders.sum__quantity - LAG(orders.sum__quantity, 1) OVER (ORDER BY orders.order_month)

See also

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