Window functions

Window functions allow you to perform calculations across sets of rows that are related to the current row, without collapsing them into a single output.

Window functions are useful for a variety of analytical tasks, such as:

Computing a running totals
Calculating a moving averages
Ranking customers by their sales volume
Computing a cumulative distribution function for a set of values

General syntax

SELECT
  aggregate_function(column_name)
    OVER (
      [PARTITION BY grouping_column]
      [ORDER BY sorting_column]
      [ROWS | RANGE expression_to_bound_rows_withing_the_group] | [window_name]
    ) AS window_result
FROM
  table_name
[WINDOW window_name AS (
  [PARTITION BY grouping_column]
  [ORDER BY sorting_column]
  [ROWS or RANGE expression_to_bound_rows_withing_the_group]
)];

Arguments:

aggregate_function(column_name): The aggregate function to apply.
PARTITION BY grouping_column: Divides the result set into partitions to which the window function is applied independently.
ORDER BY sorting_column: Defines the logical order of rows within each partition.
ROWS or RANGE: Specifies the frame over which the function operates relative to the current row.
WINDOW clause: Allows you to name and reuse window definitions.

Visualizing window frames

          PARTITION
------------------------------  <-- UNBOUNDED PRECEDING (Start of Partition)
│                           │
│                           │
│===========================│  <-- N PRECEDING
│    N Rows Before Current  │
│         Row               │
│~~~~~~~~~~~~~~~~~~~~~~~~~~~│  <-- CURRENT ROW
│    M Rows After Current   │
│         Row               │
│===========================│  <-- M FOLLOWING
│                           │
│                           │
------------------------------  <-- UNBOUNDED FOLLOWING (End of Partition)

Frame bounds

Frame bounds define the start and end of the window frame for each row. Here are common examples:

Frame bound	Explanation	Example
`BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING`	Includes all rows in the partition	`SUM(value) OVER (PARTITION BY group)`
`No bound expression, no ORDER BY`	Same as above, includes all rows in the partition	`AVG(price) OVER (PARTITION BY category)`
`BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW`	From start of partition to current row	`SUM(sales) OVER (ORDER BY date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)`
`No bound expression, with ORDER BY`	Same as above, from start of partition to current row	`SUM(sales) OVER (ORDER BY date)`
`BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`	From current row to end of partition	`MAX(price) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)`
`BETWEEN 1 PRECEDING AND CURRENT ROW`	Sliding frame of current and previous row	`AVG(value) OVER (ORDER BY time ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)`
`BETWEEN 1 PRECEDING AND 1 FOLLOWING`	Sliding frame of 3 rows	`MEDIAN(score) OVER (ORDER BY id ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)`
`BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING`	From current row to end of partition	`LAST_VALUE(price) OVER (ORDER BY date ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING)`

Some functions like ROW_NUMBER() do not respect the frame and always operate on the entire partition.

Examples

Compare to category average

Compare each taco’s price to the average for its category.

SELECT
	taco_name,
	price,
	category,
	avg(price) OVER (PARTITION BY category) AS categoryAvg,
	price - categoryAvg AS diff
FROM
	taco_menu;

Returns:

| taco_name      | price | category | categoryAvg | diff |
|----------------|-------|----------|-------------|------|
| Supreme Taco   | 9.99  | Meat     | 7.99        | 2.00 |
| Classic Beef   | 6.99  | Meat     | 7.99        | -1.00|
| Chicken Fiesta | 6.99  | Meat     | 7.99        | -1.00|
| Veggie Delight | 7.99  | Veggie   | 6.49        | 1.50 |
| Bean Bonanza   | 5.99  | Veggie   | 6.49        | -0.50|

Compare to category max

Compare each taco’s price to the maximum for its category.

SELECT
	taco_name,
	price,
	category,
	MAX(price) OVER (PARTITION BY category) AS categoryMax,
	price - categoryMax AS diff
FROM
	taco_menu;

Returns:

| taco_name      | price | category | categoryMax | diff |
|----------------|-------|----------|-------------|------|
| Supreme Taco   | 9.99  | Meat     | 9.99        |  0.00|
| Classic Beef   | 6.99  | Meat     | 9.99        | -3.00|
| Chicken Fiesta | 7.99  | Meat     | 9.99        | -2.00|
| Veggie Delight | 7.99  | Veggie   | 7.99        |  0.00|
| Bean Bonanza   | 5.99  | Veggie   | 7.99        | -2.00|

Partitioning by column

SELECT
	category,
	price,
	spice_level,
	groupArray(price) OVER (
		PARTITION BY category
	) AS category_prices
FROM
	taco_menu
ORDER BY
	category ASC,
	price DESC

Returns:

| category | price | spice_level | category_prices   |
|----------|-------|-------------|-------------------|
| Meat     | 9.99  | 3           | [9.99,8.99,7.99]  | ┐
| Meat     | 8.99  | 2           | [9.99,8.99,7.99]  | │ Meat category
| Meat     | 7.99  | 1           | [9.99,8.99,7.99]  | ┘
| Veggie   | 8.99  | 2           | [8.99,7.99]       | ┐ Veggie category
| Veggie   | 7.99  | 1           | [8.99,7.99]       | ┘

Cumulative sum

SELECT
	taco_name,
	ts,
	quantity,
	SUM(quantity) OVER (
		PARTITION BY taco_name
		ORDER BY ts ASC
	) AS stock_balance
FROM
	taco_inventory
ORDER BY
	taco_name ASC,
	ts ASC;

Returns:

| taco_name | ts                  | quantity | stock_balance |
|-----------|---------------------|----------|---------------|
| al_pastor | 2020-01-01 00:00:00 |        1 |             1 |
| al_pastor | 2020-01-02 00:00:00 |        2 |             3 |
| al_pastor | 2020-01-03 00:00:00 |        3 |             6 |
| carnitas  | 2020-01-01 00:00:00 |        5 |             5 |
| carnitas  | 2020-01-02 00:00:00 |        1 |             6 |
| carnitas  | 2020-01-03 00:00:00 |       -2 |             4 |

Moving average over 5 rows

SELECT
	taco_type,
	ts,
	sales,
	AVG(sales) OVER (
		PARTITION BY taco_type
		ORDER BY ts ASC
		ROWS BETWEEN 4 PRECEDING AND CURRENT ROW
	) AS moving_avg_sales
FROM
	taco_sales
ORDER BY
	taco_type ASC,
	ts ASC;

Returns:

| taco_type | ts                  | sales | moving_avg_sales |
|-----------|---------------------|-------|------------------|
| al_pastor | 2020-01-01 00:00:00 | 1.00  | 1.00             |
| al_pastor | 2020-01-02 00:00:00 | 2.00  | 1.50             |
| al_pastor | 2020-01-03 00:00:00 | 3.00  | 2.00             |
| al_pastor | 2020-01-04 00:00:00 | 4.00  | 2.50             |
| al_pastor | 2020-01-05 00:00:00 | 5.00  | 3.00             |
| al_pastor | 2020-01-06 00:00:00 | 6.00  | 4.00             |
| al_pastor | 2020-01-07 00:00:00 | 7.00  | 5.00             |
| al_pastor | 2020-01-08 00:00:00 | 8.00  | 6.00             |

Moving average over 10 seconds

SELECT
    taco_type,
    ts,
    tacos_sold,
    AVG(tacos_sold) OVER (
        PARTITION BY taco_type
        ORDER BY ts
        RANGE BETWEEN 10 PRECEDING AND CURRENT ROW
    ) AS moving_avg_10_seconds_tacos
FROM
    taco_sales
ORDER BY
    taco_type ASC,
    ts ASC;

Returns:

| taco_type | ts                  | tacos_sold | moving_avg_10_seconds_tacos |
|-----------|---------------------|------------|----------------------------|
| al_pastor | 2020-01-01 00:00:00 | 5          | 5.00                       |
| al_pastor | 2020-01-01 00:01:10 | 3          | 4.00                       |
| al_pastor | 2020-01-01 00:02:20 | 7          | 5.00                       |
| al_pastor | 2020-01-01 00:03:30 | 4          | 4.75                       |
| carnitas  | 2020-01-01 00:00:00 | 6          | 6.00                       |
| carnitas  | 2020-01-01 00:01:10 | 4          | 5.00                       |
| carnitas  | 2020-01-01 00:02:20 | 8          | 6.00                       |
| carnitas  | 2020-01-01 00:03:30 | 5          | 5.75                       |

Moving average over 10 days

SELECT
    taco_type,
    ts,
    tacos_sold,
    ROUND(AVG(tacos_sold) OVER (
        PARTITION BY taco_type
        ORDER BY toDate(ts)
        RANGE BETWEEN 10 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_10_days_tacos
FROM taco_sales
ORDER BY taco_type ASC, ts ASC;

Returns:

| taco_type | ts                  | tacos_sold | moving_avg_10_days_tacos |
|-----------|---------------------|------------|--------------------------|
| al_pastor | 2020-01-01 00:00:00 | 50         | 50.00                    |
| al_pastor | 2020-01-01 12:00:00 | 45         | 47.50                    |
| al_pastor | 2020-01-02 11:00:00 | 55         | 50.00                    |
| al_pastor | 2020-01-02 12:00:00 | 60         | 52.50                    |
| al_pastor | 2020-02-01 10:00:00 | 70         | 70.00                    |
| al_pastor | 2020-02-01 12:00:00 | 65         | 67.50                    |
| al_pastor | 2020-02-10 12:00:00 | 80         | 71.67                    |
| al_pastor | 2020-02-10 13:00:00 | 75         | 72.50                    |
| al_pastor | 2020-02-20 12:00:01 | 90         | 76.00                    |
| al_pastor | 2020-03-01 12:00:00 | 85         | 85.00                    |
| al_pastor | 2020-03-01 12:00:00 | 95         | 90.00                    |
| al_pastor | 2020-03-01 12:00:00 | 100        | 93.33                    |

ClickHouse function reference

row_number

Returns a unique number for each row within its partition, starting at 1 for the first row.

Syntax:

row_number()
  OVER (
    [
      [PARTITION BY grouping_column]
      [ORDER BY sorting_column]
      [ROWS or RANGE expression_to_bound_rows_withing_the_group]
    ] | [window_name]
  )
)

Arguments:

None other than the OVER clause.

Returns:

A unique integer for each row within its partition. (UInt64)

Example:

SELECT
	taco_name,
	price,
	row_number() OVER (ORDER BY price DESC) AS price_rank
FROM
	taco_menu;

Result:

| taco_name      | price | price_rank |
|----------------|-------|------------|
| Supreme Taco   | 9.99  | 1          |
| Veggie Delight | 7.99  | 2          |
| Classic Beef   | 6.99  | 3          |
| Chicken Fiesta | 6.99  | 4          |
| Bean Bonanza   | 5.99  | 5          |

In this example, row_number() assigns a unique rank to each taco based on its price in descending order. Note that even though “Classic Beef” and “Chicken Fiesta” have the same price, they receive different rank numbers.

Example with PARTITION BY:

SELECT
	category,
	taco_name,
	price,
	row_number() OVER (PARTITION BY category ORDER BY price DESC) AS category_price_rank
FROM
	taco_menu;

Result:

| category | taco_name      | price | category_price_rank |
|----------|----------------|-------|---------------------|
| Meat     | Supreme Taco   | 9.99  | 1                   |
| Meat     | Classic Beef   | 6.99  | 2                   |
| Meat     | Chicken Fiesta | 6.99  | 3                   |
| Veggie   | Veggie Delight | 7.99  | 1                   |
| Veggie   | Bean Bonanza   | 5.99  | 2                   |

In this example, row_number() restarts the numbering for each taco category, ranking tacos by price within their respective categories.

Unlike rank() and dense_rank(), row_number() always assigns unique, sequential numbers, even for rows with identical values in the ORDER BY column.

first_value

Returns the first value evaluated within its ordered frame. By default, NULL arguments are skipped, but this behavior can be modified.

Syntax:

first_value(column_name) [RESPECT NULLS | IGNORE NULLS]
  OVER (
    [
      [PARTITION BY grouping_column]
      [ORDER BY sorting_column]
      [ROWS or RANGE expression_to_bound_rows_withing_the_group]
    ] | [window_name]
  )

Alias:

Arguments:

column_name (Any): The column from which to select the first value.

Optional Modifiers:

RESPECT NULLS: Includes NULL values in the evaluation.
IGNORE NULLS: Skips NULL values (default behavior).

Returns:

The first value in the ordered set of rows.

Example:

SELECT
	taco_name,
	price,
	first_value(taco_name) OVER (ORDER BY price DESC) AS most_expensive_taco
FROM
	taco_menu;

Result:

| taco_name    | price | most_expensive_taco |
|--------------|-------|---------------------|
| Supreme Taco | 12.99 | Supreme Taco        |
| Deluxe Taco  | 10.99 | Supreme Taco        |
| Classic Taco |  8.99 | Supreme Taco        |
| Basic Taco   |  6.99 | Supreme Taco        |

In this example, first_value identifies the most expensive taco (Supreme Taco) based on the descending order of prices.

first_value is an alias for the any function in ClickHouse.
When using RESPECT NULLS, NULL values are considered in determining the first value.

This function is particularly useful for finding the top or bottom value within a group or partition in ordered data sets.

last_value

Returns the last value evaluated within its ordered frame. By default, NULL arguments are skipped, but this behavior can be modified using the RESPECT NULLS clause.

Syntax:

last_value(column_name) [RESPECT NULLS | IGNORE NULLS]
  OVER (
    [
      [PARTITION BY grouping_column]
      [ORDER BY sorting_column]
      [ROWS or RANGE expression_to_bound_rows_withing_the_group]
    ] | [window_name]
)

Arguments:

column_name: The column from which to retrieve the last value.

Optional Modifiers:

RESPECT NULLS: Includes NULL values in the evaluation.
IGNORE NULLS: Skips NULL values (default behavior).

Returns:

The last value evaluated within the specified window frame.

Example:

SELECT
	taco_name,
	price,
	last_value(taco_name) OVER (
		ORDER BY price DESC
		RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
	) AS cheapest_taco
FROM taco_menu;

Result:

| taco_name    | price | cheapest_taco |
|--------------|-------|---------------|
| Supreme Taco | 12.99 | Basic Taco    |
| Deluxe Taco  | 10.99 | Basic Taco    |
| Veggie Taco  |  8.99 | Basic Taco    |
| Classic Taco |  7.99 | Basic Taco    |
| Basic Taco   |  5.99 | Basic Taco    |

In this example, last_value is used to find the cheapest taco from our taco menu. The window frame covers the entire dataset, so the last value (when ordered by price descending) is always the cheapest taco.

nth_value

Returns the nth value evaluated within its ordered frame.

Syntax:

nth_value (x, offset)
  OVER (
    [
      [PARTITION BY grouping_column]
      [ORDER BY sorting_column]
      [ROWS or RANGE expression_to_bound_rows_withing_the_group]
    ] | [window_name]
  )

Arguments:

x (Any data type): The column or expression to evaluate.
offset (UInt64): The nth row to evaluate the current row against (1-based).

Returns:

The first non-NULL value from the nth row in the ordered frame.

Example:

SELECT
	taco_name,
	price,
	nth_value(taco_name, 3) OVER (
		ORDER BY price DESC
	) AS third_most_expensive_taco
FROM taco_menu;

Result:

| taco_name     | price | third_most_expensive_taco |
|---------------|-------|---------------------------|
| Supreme Taco  | 12.99 | NULL                      |
| Deluxe Taco   | 11.99 | NULL                      |
| Gourmet Taco  | 10.99 | Gourmet Taco              |
| Classic Taco  |  8.99 | Gourmet Taco              |
| Budget Taco   |  6.99 | Gourmet Taco              |

In this example, nth_value is used to find the third most expensive taco from a taco menu. The function returns NULL for the first two rows because there isn’t a third row yet in the ordered frame. From the third row onward, it returns ‘Gourmet Taco’, which is the taco name in the third row when ordered by price descending.

The nth_value function is particularly useful when you need to compare each row with a specific nth value within a sorted set, such as finding benchmark or threshold values in a dataset.

rank

Ranks rows within a partition, allowing for gaps in the ranking sequence.

It assigns a unique rank to each distinct row within a partition based on the specified ordering. If multiple rows have the same values in the ORDER BY columns, they receive the same rank, and the next rank is skipped. This results in gaps in the ranking sequence.

For ranking without gaps, use the dense_rank() function instead.

Syntax:

rank()
  OVER (
      [
        [PARTITION BY grouping_column]
        [ORDER BY sorting_column]
        [ROWS or RANGE expression_to_bound_rows_withing_the_group]
      ] | [window_name]
    )
  FROM table_name
  WINDOW window_name as ([
    [PARTITION BY grouping_column]
    [ORDER BY sorting_column]
  ])

Arguments:

None other than the OVER clause.

Returns:

A rank value for each row. [UInt64]

Example:

SELECT
	taco_name,
	price,
	rank() OVER (ORDER BY price DESC) AS price_rank
FROM
	taco_menu;

Result:

| taco_name         | price | price_rank |
|-------------------|-------|------------|
| Supreme Taco      | 12.99 | 1          |
| Deluxe Taco       | 12.99 | 1          |
| Veggie Fiesta     |  9.99 | 3          |
| Classic Beef Taco |  8.99 | 4          |
| Bean Bonanza      |  7.99 | 5          |

In this example:

Tacos are ranked by price in descending order.
The “Supreme Taco” and “Deluxe Taco” have the same price, so they both receive rank 1.
The next rank is 3, skipping 2, demonstrating the gap in ranking.

The rank() function is often used for competitive rankings or when you need to identify distinct positions while preserving information about ties.

dense_rank

Ranks rows within a partition without gaps in the ranking sequence.

It assigns a unique rank to each distinct row within a partition based on the ORDER BY clause. If multiple rows have the same values, they receive the same rank, and the next rank is assigned to the next distinct value without gaps.

This function differs from rank() in that it doesn’t leave gaps in the ranking sequence when there are ties.

Syntax:

dense_rank()
  OVER (
    [
      [PARTITION BY grouping_column]
      [ORDER BY sorting_column]
      [ROWS or RANGE expression_to_bound_rows_withing_the_group]
    ] | [window_name]
  )
FROM table_name
WINDOW window_name as ([
  [PARTITION BY grouping_column]
  [ORDER BY sorting_column]
])

Alias:

denseRank (case-sensitive)

Arguments:

None other than the OVER clause.

Returns:

A ranking number for each row within its partition, without gaps. [UInt64]

Example:

SELECT
	taco_name,
	price,
	dense_rank() OVER (ORDER BY price DESC) AS price_rank
FROM
	taco_menu;

Result:

| taco_name         | price | price_rank |
|-------------------|-------|------------|
| Supreme Taco      | 12.99 | 1          |
| Deluxe Taco       | 12.99 | 1          |
| Veggie Fiesta     | 10.99 | 2          |
| Classic Beef Taco |  8.99 | 3          |
| Chicken Taco      |  8.99 | 3          |
| Basic Taco        |  6.99 | 4          |

In this example:

The most expensive tacos (Supreme and Deluxe) both receive rank 1.
The next distinct price (Veggie Fiesta) receives rank 2, without gaps.
Classic Beef and Chicken tacos share rank 3.
The least expensive taco (Basic) receives rank 4.

dense_rank() is often used in scenarios where you want to assign rankings without gaps, such as in competition results or when creating tier systems based on certain criteria.

percent_rank

Returns the relative rank (i.e., percentile) of rows within a window partition.

Syntax:

percent_rank(column_name)
  OVER (
    [
      [PARTITION BY grouping_column]
      [ORDER BY sorting_column]
      [ROWS or RANGE expression_to_bound_rows_withing_the_group]
    ] | [window_name]
  )

Alias:

percentRank

Arguments:

column_name (String): The column to calculate the percent rank for.

Returns:

A float value between 0 and 1, representing the relative rank of each row.

The default and required window frame definition is RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.
The result is calculated as (rank - 1) / (total_rows - 1), where rank is the row’s rank within the partition and total_rows is the number of rows in the partition.

Example:

SELECT
	taco_name,
	price,
	percent_rank() OVER (ORDER BY price DESC) AS price_percentile
FROM
	taco_menu;

Result:

| taco_name     | price | price_percentile  |
|---------------|-------|-------------------|
| Supreme Taco  | 12.99 | 0                 |
| Deluxe Taco   | 10.99 | 0.2               |
| Veggie Taco   |  8.99 | 0.4               |
| Classic Taco  |  7.99 | 0.6               |
| Basic Taco    |  5.99 | 0.8               |
| Value Taco    |  3.99 | 1                 |

In this example, percent_rank() calculates the price percentile for each taco. The most expensive taco (Supreme Taco) has a percentile of 0, while the least expensive (Value Taco) has a percentile of 1. The other tacos are distributed between these extremes based on their prices.

lagInFrame

Returns a value evaluated at the row that is at a specified physical offset row before the current row within the ordered frame.

The lagInFrame function is similar to the common lag function found in databases like PostgreSQL or Snowflake, but it respects the window frame. This means it only considers rows within the defined frame, not the entire partition. To achieve equivalent behavior to the traditional lag function use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING in the OVER clause.

Syntax:

lagInFrame(x[, offset[, default]])
  OVER (
    [
      [PARTITION BY grouping_column]
      [ORDER BY sorting_column]
      [ROWS or RANGE expression_to_bound_rows_withing_the_group]
    ] | [window_name]
)

Arguments:

x: Column or expression to evaluate.
offset ((U)Int*, optional): Number of rows to look back. Default is 1.
default (optional): Value to return if the offset exceeds the window frame boundaries. Defaults to the column’s default value.

Returns:

The value of x from the row offset rows before the current row within the ordered frame.

Example:

This example calculates the day-to-day change in closing price for a taco restaurant’s stock:

SELECT
	date,
	close,
	lagInFrame(close, 1, close) OVER (ORDER BY date ASC) AS previous_day_close,
	ROUND(close - previous_day_close, 2) AS price_change,
	ROUND((close - previous_day_close) / previous_day_close * 100, 2) AS percent_change
FROM
	taco_stock_prices
ORDER BY
	date DESC
LIMIT 5;

Result:

| date       | close | previous_day_close | price_change | percent_change |
|------------|-------|---------------------|--------------|----------------|
| 2024-06-07 | 42.89 | 43.00               | -0.11        | -0.26          |
| 2024-06-06 | 43.00 | 44.44               | -1.44        | -3.24          |
| 2024-06-05 | 44.44 | 38.44               |  6.00        | 15.61          |
| 2024-06-04 | 38.44 | 37.00               |  1.44        |  3.89          |
| 2024-06-03 | 37.00 | 37.00               |  0.00        |  0.00          |

In this example:

close is the closing price for each day.
previous_day_close uses lagInFrame to get the previous day’s closing price.
price_change calculates the absolute change in price.
percent_change calculates the percentage change in price.

leadInFrame

Returns a value evaluated at the row that is offset rows after the current row within the ordered frame.

The leadInFrame function is similar to the common lead function found in databases like PostgreSQL or Snowflake, but it respects the window frame. This means it only considers rows within the defined frame, not the entire partition. To achieve equivalent behavior to the traditional lead function use ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING in the OVER clause.

Syntax:

leadInFrame(x[, offset[, default]])
  OVER (
    [
      [PARTITION BY grouping_column]
      [ORDER BY sorting_column]
      [ROWS or RANGE expression_to_bound_rows_withing_the_group]
    ] | [window_name]
)

Arguments:

x: Column or expression to evaluate.
offset (UInt*, optional): Number of rows to look ahead. Default is 1.
default (optional): Value to return if the offset exceeds the window frame. Defaults to the column’s default value.

Returns:

The value of x evaluated at the row that is offset rows after the current row within the ordered frame.

Example:

This example uses leadInFrame to show the next year’s taco sales for each taco type:

SELECT
	taco_type,
	year,
	sales,
	leadInFrame(sales) OVER (
		PARTITION BY taco_type
		ORDER BY year
	) AS next_year_sales
FROM
	taco_sales
ORDER BY
	taco_type,
	year
LIMIT 5;

Result:

| taco_type | year | sales | next_year_sales |
|-----------|------|-------|-----------------|
| Al Pastor | 2020 | 1000  | 1200            |
| Al Pastor | 2021 | 1200  | 1500            |
| Al Pastor | 2022 | 1500  | NULL            |
| Carnitas  | 2020 | 800   | 950             |
| Carnitas  | 2021 | 950   | 1100            |

In this example:

next_year_sales shows the sales for the following year for each taco type.
NULL appears when there’s no next year in the data for that taco type.

Introduction

Statements

Functions

General syntax

Visualizing window frames

Frame bounds

Examples

Compare to category average

Compare to category max

Partitioning by column

Cumulative sum

Moving average over 5 rows

Moving average over 10 seconds

Moving average over 10 days

ClickHouse function reference

row_number

first_value

last_value

nth_value

rank

dense_rank

percent_rank

lagInFrame

leadInFrame

Introduction

Statements

Functions

​General syntax

​Visualizing window frames

​Frame bounds

​Examples

​Compare to category average

​Compare to category max

​Partitioning by column

​Cumulative sum

​Moving average over 5 rows

​Moving average over 10 seconds

​Moving average over 10 days

​ClickHouse function reference

​row_number

​first_value

​last_value

​nth_value

​rank

​dense_rank

​percent_rank

​lagInFrame

​leadInFrame

General syntax

Visualizing window frames

Frame bounds

Examples

Compare to category average

Compare to category max

Partitioning by column

Cumulative sum

Moving average over 5 rows

Moving average over 10 seconds

Moving average over 10 days

ClickHouse function reference

row_number

first_value

last_value

nth_value

rank

dense_rank

percent_rank

lagInFrame

leadInFrame