A window works out a value for each result row from other rows of the result. The Windows menu adds running totals, moving averages and ranks. It also adds the value from the row before or after, the change from the row before, and shares of the total. In a query, each one is a custom calculation: it reads the query’s result rows, after the query groups them.

How it works

A window has three parts, which SQL writes in its OVER clause:

Syntax

1
KATER__FUNCTION(EVAL(measure), arguments) OVER (PARTITION BY dimension ORDER BY dimension ASC)

• Give an order. A running total, a lag, a lead, a delta and a first or last value read the rows in order. Without an order, the warehouse reads them in any order.
• Break ties. When two rows can have the same order value, the order between them can change from run to run. For a row number, a lag or a moving window, add a second key so the order is stable. RANK keeps ties on purpose: equal values share a rank.
• A frame counts rows, not periods. A trailing window of 3 reads this row and the two rows before it. When a month has no rows, the window reaches one more month back. To compare calendar periods, use a period comparison.

Window functions

• Rank, Row Number and Share of Total take only a partition. Write OVER () or OVER (PARTITION BY …). The others take an ORDER BY and an optional PARTITION BY.
• The menu writes the format. “The field’s format” is the format of the field the window reads. A field with no format gets number(2) for an average, or for a value that is not a whole number, and no format otherwise. A currency(currency, 2) becomes number(2) when the SELECT has no Currency dimension. See Formats, descriptions and AI context.
• The menu names the field. A window gets a name from its function and its field, such as running_sum__sum__quantity, and a label such as “Units (Running Sum)”.

The Windows menu

Right-click a field in the field list, or on its column, and open Windows. A number offers Window Aggregation, Lag, Lead, Delta, Percent Delta, Rank, Row Number and Share of Total. A text or a date offers Lag, Lead, First Value and Last Value.

Each item opens the New calculation dialog, filled in. Turn it into names the window in plain words, such as Running total or Moving average. Under Settings, Along is the window’s order and Start over for each is its partition. For Share of Total, the partition is Out of the total for each. For Rank and Row Number, it is Rank within each. Select Create calculation to add the field to the query.

• Start over for each starts filled in. The dialog fills it with the query’s other dimensions, but not its dates. So a running total of Revenue by month and currency starts over for each currency. Empty it for one window across every row.
• Any window can be written by hand. Plain SQL such as SUM(EVAL(shop.sum__quantity)) OVER (ORDER BY shop.order_month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) is a custom calculation too. When it means the same as one of the functions above, its menu reopens it in that function’s form, here Window Aggregation. Without the ROWS frame, SQL’s default frame is not the same, so it does not reopen.

Examples

A running total

How many units has the shop sold so far, month by month?

1
SELECT
2
  shop.order_month,
3
  EVAL(shop.sum__quantity),
4
  CUSTOM_CALCULATION(
5
    'running_sum__sum__quantity',
6
    KATER__WINDOW_AGGREGATION(EVAL(shop.sum__quantity), 'sum', 'running') OVER (ORDER BY shop.order_month ASC)
7
  ) AS "Units (Running Sum)"
8
FROM workspace.shop AS shop
9
GROUP BY
10
  shop.order_month
11
ORDER BY
12
  1

The first 5 of 21 rows. Each row adds its month’s units to the total of the rows before it: 455 + 501 = 956. The last row, September 2026, reads 9,154, all the units the shop sold.

The shop’s monthly_revenue query declares its running total with CUSTOM_CALCULATION, as the menu does. A window declared with DIMENSION is published as a custom calculation anyway, with the warning custom_calculation_declared_as_dimension.

A moving average of the last 3 months

What is the average of this month’s units and the two months before?

1
SELECT
2
  shop.order_month,
3
  EVAL(shop.sum__quantity),
4
  CUSTOM_CALCULATION(
5
    'trailing_3_avg__sum__quantity',
6
    KATER__WINDOW_AGGREGATION(EVAL(shop.sum__quantity), 'avg', 'trailing', 3) OVER (ORDER BY shop.order_month ASC)
7
  ) AS "Units (Trailing 3 Average)" /* @format number(2) */
8
FROM workspace.shop AS shop
9
GROUP BY
10
  shop.order_month
11
ORDER BY
12
  1

The first 5 of 21 rows. The first two rows have fewer rows before them, so they average fewer months: (455 + 501) / 2 = 478 for February. From March on, each row averages three months: (501 + 406 + 476) / 3 = 461 for April. Units has no format, so the average gets number(2).

The row before and the row after

What were last month’s orders, and next month’s, beside this month’s?

1
SELECT
2
  shop.order_month,
3
  EVAL(shop.count_distinct__order_id),
4
  CUSTOM_CALCULATION(
5
    'lag__count_distinct__order_id',
6
    KATER__LAG(EVAL(shop.count_distinct__order_id)) OVER (ORDER BY shop.order_month ASC)
7
  ) AS "Orders (Lag)",
8
  CUSTOM_CALCULATION(
9
    'lead__count_distinct__order_id',
10
    KATER__LEAD(EVAL(shop.count_distinct__order_id)) OVER (ORDER BY shop.order_month ASC)
11
  ) AS "Orders (Lead)"
12
FROM workspace.shop AS shop
13
GROUP BY
14
  shop.order_month
15
ORDER BY
16
  1

The first 5 of 21 rows. January 2025 has no row before it, so its lag is null. In the same way, the last row, September 2026, has no row after it, and its lead is null. KATER__LAG(x, 12) reads 12 rows back.

A lag reads the row before, not the month before. When a month has no rows, the row before is an earlier month, and the lag reads it as if it were last month. A period comparison reads the calendar month before, and it stays empty when that month has no rows. See Missing periods.

Each region’s top and bottom channel

Which channel brings each region the most orders, and which the fewest? First Value and Last Value read a text or a date, so this window reads Channel, in the order of Orders.

1
SELECT
2
  shop.region,
3
  shop.channel,
4
  EVAL(shop.count_distinct__order_id),
5
  CUSTOM_CALCULATION(
6
    'first_value__channel',
7
    KATER__FIRST_VALUE(shop.channel) OVER (PARTITION BY shop.region ORDER BY EVAL(shop.count_distinct__order_id) DESC)
8
  ) AS "Channel (First Value)",
9
  CUSTOM_CALCULATION(
10
    'last_value__channel',
11
    KATER__LAST_VALUE(shop.channel) OVER (PARTITION BY shop.region ORDER BY EVAL(shop.count_distinct__order_id) DESC)
12
  ) AS "Channel (Last Value)"
13
FROM workspace.shop AS shop
14
WHERE
15
  shop.region IN ('APAC', 'LATAM')
16
GROUP BY
17
  shop.region,
18
  shop.channel
19
ORDER BY
20
  1,
21
  3 DESC

The window starts over for each region and reads its channels from the most orders to the fewest. In APAC, email comes first and paid comes last. Last Value reads the whole partition, not only the rows up to this row, so every row of a region shows the same value.

The change from the row before

How many more orders did each month have than the month before, and by what percent?

1
SELECT
2
  shop.order_month,
3
  EVAL(shop.count_distinct__order_id),
4
  CUSTOM_CALCULATION(
5
    'delta__count_distinct__order_id',
6
    KATER__DELTA(EVAL(shop.count_distinct__order_id)) OVER (ORDER BY shop.order_month ASC)
7
  ) AS "Orders (Delta)",
8
  CUSTOM_CALCULATION(
9
    'percent_delta__count_distinct__order_id',
10
    KATER__PERCENT_DELTA(EVAL(shop.count_distinct__order_id)) OVER (ORDER BY shop.order_month ASC)
11
  ) AS "Orders (Percent Delta)" /* @format percent(1) */
12
FROM workspace.shop AS shop
13
GROUP BY
14
  shop.order_month
15
ORDER BY
16
  1

The first 5 of 21 rows. February had 14 more orders than January, and 14 / 141 = 9.9%. January has no row before it, so both values are null. Percent Delta divides by the absolute value of the row before, so a rise from a negative value is positive. A row before that is 0 or null gives null. Percent Delta is a fraction, so never multiply it by 100: percent(1) shows 0.099 as 9.9%.

Rank and Row Number

How do the channels rank by orders in September 2026?

1
SELECT
2
  shop.channel,
3
  EVAL(shop.count_distinct__order_id),
4
  CUSTOM_CALCULATION(
5
    'rank__count_distinct__order_id',
6
    KATER__RANK(EVAL(shop.count_distinct__order_id), 'desc') OVER ()
7
  ) AS "Orders (Rank)", /* @format number(0) */
8
  CUSTOM_CALCULATION(
9
    'row_number__count_distinct__order_id',
10
    KATER__ROW_NUMBER(EVAL(shop.count_distinct__order_id), 'desc') OVER ()
11
  ) AS "Orders (Row Number)" /* @format number(0) */
12
FROM workspace.shop AS shop
13
WHERE
14
  shop.order_date BETWEEN '2026-09-01' AND '2026-09-30'
15
GROUP BY
16
  shop.channel
17
ORDER BY
18
  3,
19
  4

Web and email both have 33 orders. RANK gives both of them 2, and the next channel gets 4. ROW_NUMBER gives them 2 and 3, in an order the warehouse sets, so the two can change places from run to run. For a stable order, write the row number in plain SQL with a second key, such as ROW_NUMBER() OVER (ORDER BY EVAL(shop.count_distinct__order_id) DESC, shop.channel).

Each region’s share of all orders

What share of the shop’s orders does each region have?

1
SELECT
2
  shop.region,
3
  EVAL(shop.count_distinct__order_id),
4
  CUSTOM_CALCULATION(
5
    'share_of_total__count_distinct__order_id',
6
    KATER__SHARE_OF_TOTAL(EVAL(shop.count_distinct__order_id)) OVER ()
7
  ) AS "Orders (Share of Total)" /* @format percent(1) */
8
FROM workspace.shop AS shop
9
GROUP BY
10
  shop.region
11
ORDER BY
12
  2 DESC

Each row’s orders are divided by the sum of the five rows, 3,000 orders. EMEA has 777 / 3,000 = 25.9%. A share of a sum or a count can become a measure. Orders is a distinct count, so this one stays a custom calculation. See Convert to a measure.

Each channel’s share within its region

What share of each region’s orders comes from each channel? A partition makes each region’s channels add up to 100% on their own.

1
SELECT
2
  shop.region,
3
  shop.channel,
4
  EVAL(shop.count_distinct__order_id),
5
  CUSTOM_CALCULATION(
6
    'share_of_total__count_distinct__order_id',
7
    KATER__SHARE_OF_TOTAL(EVAL(shop.count_distinct__order_id)) OVER (PARTITION BY shop.region)
8
  ) AS "Orders (Share of Total)" /* @format percent(1) */
9
FROM workspace.shop AS shop
10
WHERE
11
  shop.region IN ('AMER', 'EMEA')
12
GROUP BY
13
  shop.region,
14
  shop.channel
15
ORDER BY
16
  1,
17
  3 DESC

AMER has 728 orders, so paid has 197 / 728 = 27.1%. The WHERE keeps two regions so the result fits here. WHERE runs before the window, so the window sees only the rows the query keeps.

A running total of Revenue for each currency

Revenue cannot be added up across currencies. See Measures you cannot add up. So a running total of Revenue must start over for each currency.

1
SELECT
2
  KATER__MONTH(shop.order_date) AS "Order Month",
3
  shop.currency,
4
  EVAL(shop.sum__amount),
5
  CUSTOM_CALCULATION(
6
    'running_sum__sum__amount',
7
    KATER__WINDOW_AGGREGATION(EVAL(shop.sum__amount), 'sum', 'running') OVER (PARTITION BY shop.currency ORDER BY KATER__MONTH(shop.order_date) ASC)
8
  ) AS "Revenue (Running Sum)" /* @format currency(currency, 2) */
9
FROM workspace.shop AS shop
10
GROUP BY
11
  KATER__MONTH(shop.order_date),
12
  shop.currency
13
ORDER BY
14
  2,
15
  1

Six of the 42 rows. The euro total ends at €84,371.35, all of EMEA’s euro revenue. The dollar total ends at $370,665.44, the dollar revenue of every region. Without PARTITION BY shop.currency, the compiler refuses the query with not_additive_across: “Revenue cannot be added up across Currency, so a total of it along the rows has to start over for each Currency. Add Currency to Start over for each (its PARTITION BY), or filter Currency to one value.”

Where a window cannot go

WHERE, GROUP BY, HAVING and JOIN conditions run before the windows. So a window cannot be in them, and they cannot read a field of the same SELECT that runs a window.

A rank filtered in HAVING

This query tries to keep the top three regions in HAVING:

1
SELECT
2
  shop.region,
3
  EVAL(shop.count_distinct__order_id),
4
  CUSTOM_CALCULATION(
5
    'rank__count_distinct__order_id',
6
    KATER__RANK(EVAL(shop.count_distinct__order_id), 'desc') OVER ()
7
  ) AS "Orders (Rank)" /* @format number(0) */
8
FROM workspace.shop AS shop
9
GROUP BY
10
  shop.region
11
HAVING
12
  rank__count_distinct__order_id <= 3

The compiler refuses it with sibling_not_visible_at_stage: “HAVING can’t use ‘rank__count_distinct__order_id’, a field declared in this SELECT, because its window is computed after HAVING. Filter on it in QUALIFY instead.” QUALIFY runs after the window. Filters › Is At Most on the rank writes it there for you. See Regions whose rank is at most 3.

Common errors