Skip to main content
DocumentationLibraryReference
On this page

Kater SQL Functions Windows

KATER__PERCENT_DELTA

The change from the row before, as a share of that row's value.

Family: Windows

Syntax 

KATER__PERCENT_DELTA(field) OVER ([PARTITION BY ...] ORDER BY ...)

Arguments

ArgumentDescription
fieldThe value it reads on each row: a field, or a measure, such as orders.sum__amount.

Returns

A fraction, such as 0.25 for 25%. The field menu writes @format percent(1) beside it, so it shows as a percentage. NULL on the first row of each partition, and where the row before is 0 or empty.

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 divides by the absolute value of the row before, so a change from -10 to -5 is +50%.
  • 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.

SELECT
  orders.order_month AS "Order Month",
  EVAL(orders.sum__quantity) AS "Units",
  CUSTOM_CALCULATION(
    'percent_delta__sum__quantity',
    KATER__PERCENT_DELTA(orders.sum__quantity) OVER (ORDER BY orders.order_month)
  ) AS "Percent Change" /* @format percent(1) */
FROM kater_dataset.orders AS orders
WHERE
  KATER__IS_ON_OR_AFTER(orders.order_date, DATE '2026-05-01')
GROUP BY
  orders.order_month
ORDER BY
  1
Order MonthUnitsPercent Change
2026-05-01441null
2026-06-01403-8.6%
2026-07-014388.7%
2026-08-014543.7%
2026-09-01442-2.6%

Compiles to

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

CAST(orders.sum__quantity - LAG(orders.sum__quantity, 1) OVER (ORDER BY orders.order_month) AS DOUBLE) / NULLIF(ABS(LAG(orders.sum__quantity, 1) OVER (ORDER BY orders.order_month)), 0)

See also