Skip to main content
DocumentationLibraryReference
On this page

Kater SQL Functions Windows

KATER__LAG

The value from an earlier row, offset rows back in the OVER's order.

Family: Windows

Syntax 

KATER__LAG(field [, offset]) OVER ([PARTITION BY ...] ORDER BY ...)

Arguments

Required

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

Optional

ArgumentDescription
offsetHow many rows back. A whole number. It is 1 when left out.

Returns

A value of the field’s own type. NULL where no row is that far back. 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. For the same month a year earlier, use KATER__SAME_PERIOD_LAST_YEAR.

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(
    'lag__sum__quantity',
    KATER__LAG(orders.sum__quantity, 1) OVER (ORDER BY orders.order_month)
  ) AS "Units Month Before"
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 MonthUnitsUnits Month Before
2026-05-01441null
2026-06-01403441
2026-07-01438403
2026-08-01454438
2026-09-01442454

Compiles to

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

LAG(orders.sum__quantity, 1) OVER (ORDER BY orders.order_month)

See also