Analytic function concepts

An analytic function computes values over a group of rows and returns a single result for each row. This is different from an aggregate function, which returns a single result for a group of rows.

An analytic function includes an `OVER` clause, which defines a window of rows around the row being evaluated. For each row, the analytic function result is computed using the selected window of rows as input, possibly doing aggregation.

With analytic functions you can compute moving averages, rank items, calculate cumulative sums, and perform other analyses.

The following functions can be used as analytic functions: navigation functions, numbering functions, and aggregate analytic functions

Analytic function syntax

```analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
{ named_window | ( [ window_specification ] ) }

window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC }  ] [, ...] ]
[ window_frame_clause ]

window_frame_clause:
{ rows_range } { frame_start | frame_between }

rows_range:
{ ROWS | RANGE }
```

Notation rules

• Square brackets "[ ]" indicate optional clauses.
• Parentheses "( )" indicate literal parentheses.
• The vertical bar "|" indicates a logical OR.
• Curly braces "{ }" enclose a set of options.
• A comma followed by an ellipsis within square brackets "[, ... ]" indicates that the preceding item can repeat in a comma-separated list.

Description

An analytic function computes results over a group of rows. You can use the following syntax to build an analytic function:

• `analytic_function_name`: The function that performs an analytic operation. For example, the numbering function `RANK()` could be used here.
• `argument_list`: Arguments that are specific to the analytic function. Some functions have them, some do not.
• `OVER`: Keyword required in the analytic function syntax preceding the `OVER` clause.
• `over_clause`: References a window that defines a group of rows in a table upon which to use an analytic function.
• `window_specification`: Defines the specifications for the window.
• `window_frame_clause`: Defines the window frame for the window.
• `rows_range`: Defines the physical rows or a logical range for a window frame.

Notes

An analytic function can appear as a scalar expression operand in two places in the query:

• The `SELECT` list. If the analytic function appears in the `SELECT` list, its argument list and `OVER` clause can't refer to aliases introduced in the same SELECT list.
• The `ORDER BY` clause. If the analytic function appears in the `ORDER BY` clause of the query, its argument list can refer to `SELECT` list aliases.

An analytic function can't refer to another analytic function in its argument list or its `OVER` clause, even indirectly through an alias.

An analytic function is evaluated after aggregation. For example, the `GROUP BY` clause and non-analytic aggregate functions are evaluated first. Because aggregate functions are evaluated before analytic functions, aggregate functions can be used as input operands to analytic functions.

Returns

A single result for each row in the input.

Defining the `OVER` clause

``````analytic_function_name ( [ argument_list ] ) OVER over_clause

over_clause:
{ named_window | ( [ window_specification ] ) }
``````

Description

The `OVER` clause references a window that defines a group of rows in a table upon which to use an analytic function. You can provide a `named_window` that is defined in your query, or you can define the specifications for a new window.

Notes

If neither a named window nor window specification is provided, all input rows are included in the window for every row.

Examples using the `OVER` clause

These queries use window specifications:

These queries use a named window:

Defining the window specification

``````window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ { ASC | DESC } ] [, ...] ]
[ window_frame_clause ]
``````

Description

Defines the specifications for the window.

• `PARTITION BY`: Breaks up the input rows into separate partitions, over which the analytic function is independently evaluated.
• Multiple partition expressions are allowed in the `PARTITION BY` clause.
• An expression can't contain floating point types, non-groupable types, constants, or analytic functions.
• If this optional clause is not used, all rows in the input table comprise a single partition.
• `ORDER BY`: Defines how rows are ordered within a partition. This clause is optional in most situations, but is required in some cases for navigation functions.
• `window_frame_clause`: For aggregate analytic functions, defines the window frame within the current partition. The window frame determines what to include in the window. If this clause is used, `ORDER BY` is required except for fully unbounded windows.

Notes

If neither the `ORDER BY` clause nor window frame clause are present, the window frame includes all rows in that partition.

For aggregate analytic functions, if the `ORDER BY` clause is present but the window frame clause is not, the following window frame clause is used by default:

``````RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
``````

For example, the following queries are equivalent:

``````SELECT book, LAST_VALUE(item)
OVER (ORDER BY year)
FROM Library
``````
``````SELECT book, LAST_VALUE(item)
OVER (
ORDER BY year
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM Library
``````

Rules for using a named window in the window specification

If you use a named window in your window specifications, these rules apply:

• The specifications in the named window can be extended with new specifications that you define in the window specification clause.
• You can't have redundant definitions. If you have an `ORDER BY` clause in the named window and the window specification clause, an error is thrown.
• The order of clauses matters. `PARTITION BY` must come first, followed by `ORDER BY` and `window_frame_clause`. If you add a named window, its window specifications are processed first.

``````--this works:
SELECT item, purchases, LAST_VALUE(item)
OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW item_window AS (ORDER BY purchases)

--this does not work:
SELECT item, purchases, LAST_VALUE(item)
OVER (item_window ORDER BY purchases) AS most_popular
FROM Produce
WINDOW item_window AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
``````
• A named window and `PARTITION BY` can't appear together in the window specification. If you need `PARTITION BY`, add it to the named window.

• You can't refer to a named window in an `ORDER BY` clause, an outer query, or any subquery.

Examples using the window specification

These queries define partitions in an analytic function:

These queries include a named window in a window specification:

These queries define how rows are ordered in a partition:

Defining the window frame clause

``````window_frame_clause:
{ rows_range } { frame_start | frame_between }

rows_range:
{ ROWS | RANGE }

frame_between:
{
BETWEEN  unbounded_preceding AND frame_end_a
| BETWEEN numeric_preceding AND frame_end_a
| BETWEEN current_row AND frame_end_b
| BETWEEN numeric_following AND frame_end_c
}

frame_start:
{ unbounded_preceding | numeric_preceding | [ current_row ] }

frame_end_a:
{ numeric_preceding | current_row | numeric_following | unbounded_following }

frame_end_b:
{ current_row | numeric_following | unbounded_following }

frame_end_c:
{ numeric_following | unbounded_following }

unbounded_preceding:
UNBOUNDED PRECEDING

numeric_preceding:
numeric_expression PRECEDING

unbounded_following:
UNBOUNDED FOLLOWING

numeric_following:
numeric_expression FOLLOWING

current_row:
CURRENT ROW
``````

The window frame clause defines the window frame around the current row within a partition, over which the analytic function is evaluated. Only aggregate analytic functions can use a window frame clause.

• `rows_range`: A clause that defines a window frame with physical rows or a logical range.

• `ROWS`: Computes the window frame based on physical offsets from the current row. For example, you could include two rows before and after the current row.
• `RANGE`: Computes the window frame based on a logical range of rows around the current row, based on the current row’s `ORDER BY` key value. The provided range value is added or subtracted to the current row's key value to define a starting or ending range boundary for the window frame. In a range-based window frame, there must be exactly one expression in the `ORDER BY` clause, and the expression must have a numeric type.

Tip: If you want to use a range with a date, use `ORDER BY` with the `UNIX_DATE()` function. If you want to use a range with a timestamp, use the `UNIX_SECONDS()`, `UNIX_MILLIS()`, or `UNIX_MICROS()` function.

• `frame_between`: Creates a window frame with a lower and upper boundary. The first boundary represents the lower boundary. The second boundary represents the upper boundary. Only certain boundary combinations can be used, as show in the preceding syntax.

• Define the beginning of the window frame with `unbounded_preceding`, `numeric_preceding`, `numeric_following`, or `current_row`.
• `unbounded_preceding`: The window frame starts at the beginning of the partition.
• `numeric_preceding` or `numeric_following`: The start of the window frame is relative to the current row.
• `current_row`: The window frame starts at the current row.
• Define the end of the window frame with `numeric_preceding`, `numeric_following`, `current_row`, or `unbounded_following`.
• `numeric_preceding` or `numeric_following`: The end of the window frame is relative to the current row.
• `current_row`: The window frame ends at the current row.
• `unbounded_following`: The window frame ends at the end of the partition.
• `frame_start`: Creates a window frame with a lower boundary. The window frame ends at the current row.

• `unbounded_preceding`: The window frame starts at the beginning of the partition.
• `numeric_preceding`: The start of the window frame is relative to the current row.
• `current_row`: The window frame starts at the current row.
• `numeric_expression`: An expression that represents a numeric type. The numeric expression must be a constant, non-negative integer or parameter.

Notes

If a boundary extends beyond the beginning or end of a partition, the window frame will only include rows from within that partition.

You can't use a window frame clause with navigation functions and numbering functions, such as `RANK()`.

Examples using the window frame clause

These queries compute values with `ROWS`:

These queries compute values with `RANGE`:

These queries compute values with a partially or fully unbound window:

These queries compute values with numeric boundaries:

These queries compute values with the current row as a boundary:

Referencing a named window

``````SELECT query_expr,
analytic_function_name ( [ argument_list ] ) OVER over_clause
FROM from_item
WINDOW named_window_expression [, ...]

over_clause:
{ named_window | ( [ window_specification ] ) }

window_specification:
[ named_window ]
[ PARTITION BY partition_expression [, ...] ]
[ ORDER BY expression [ ASC | DESC [, ...] ]
[ window_frame_clause ]

named_window_expression:
named_window AS { named_window | ( [ window_specification ] ) }
``````

A named window represents a group of rows in a table upon which to use an analytic function. A named window is defined in the `WINDOW` clause, and referenced in an analytic function's `OVER` clause. In an `OVER` clause, a named window can appear either by itself or embedded within a window specification.

Examples

Navigation functions generally compute some `value_expression` over a different row in the window frame from the current row. The `OVER` clause syntax varies across navigation functions.

Requirements for the `OVER` clause:

• `PARTITION BY`: Optional.
• `ORDER BY`:
1. Disallowed for `PERCENTILE_CONT` and `PERCENTILE_DISC`.
2. Required for `FIRST_VALUE`, `LAST_VALUE`, `NTH_VALUE`, `LEAD` and `LAG`.
• `window_frame_clause`:
1. Disallowed for `PERCENTILE_CONT`, `PERCENTILE_DISC`, `LEAD` and `LAG`.
2. Optional for `FIRST_VALUE`, `LAST_VALUE`, and `NTH_VALUE`.

For all navigation functions, the result data type is the same type as `value_expression`.

Numbering function concepts

Numbering functions assign integer values to each row based on their position within the specified window.

Example of `RANK()`, `DENSE_RANK()`, and `ROW_NUMBER()`:

``````WITH Numbers AS
(SELECT 1 as x
UNION ALL SELECT 2
UNION ALL SELECT 2
UNION ALL SELECT 5
UNION ALL SELECT 8
UNION ALL SELECT 10
UNION ALL SELECT 10
)
SELECT x,
RANK() OVER (ORDER BY x ASC) AS rank,
DENSE_RANK() OVER (ORDER BY x ASC) AS dense_rank,
ROW_NUMBER() OVER (ORDER BY x) AS row_num
FROM Numbers

+---------------------------------------------------+
| x          | rank       | dense_rank | row_num    |
+---------------------------------------------------+
| 1          | 1          | 1          | 1          |
| 2          | 2          | 2          | 2          |
| 2          | 2          | 2          | 3          |
| 5          | 4          | 3          | 4          |
| 8          | 5          | 4          | 5          |
| 10         | 6          | 5          | 6          |
| 10         | 6          | 5          | 7          |
+---------------------------------------------------+
``````
• `RANK()`: For x=5, `rank` is 4, since `RANK()` increments by the number of peers in the previous window ordering group.
• `DENSE_RANK()`: For x=5, `dense_rank` is 3, since `DENSE_RANK()` always increments by 1, never skipping a value.
• `ROW_NUMBER()`: For x=5, `row_num` is 4.

Aggregate analytic function concepts

An aggregate function is a function that performs a calculation on a set of values. Most aggregate functions can be used in an analytic function. These aggregate functions are called aggregate analytic functions.

With aggregate analytic functions, the `OVER` clause is appended to the aggregate function call; the function call syntax remains otherwise unchanged. Like their aggregate function counterparts, these analytic functions perform aggregations, but specifically over the relevant window frame for each row. The result data types of these analytic functions are the same as their aggregate function counterparts.

Filtering results with the QUALIFY clause

The `QUALIFY` clause can be used to filter the results of an analytic function. For more information and examples, see the `QUALIFY` clause.

Analytic function examples

In these examples, the highlighted item is the current row. The bolded items are the rows that are included in the analysis.

Common tables used in examples

The following tables are used in the subsequent aggregate analytic query examples: `Produce`, `Employees`, and `Farm`.

Produce table

Some examples reference a table called `Produce`:

``````WITH Produce AS
(SELECT 'kale' as item, 23 as purchases, 'vegetable' as category
UNION ALL SELECT 'banana', 2, 'fruit'
UNION ALL SELECT 'cabbage', 9, 'vegetable'
UNION ALL SELECT 'apple', 8, 'fruit'
UNION ALL SELECT 'leek', 2, 'vegetable'
UNION ALL SELECT 'lettuce', 10, 'vegetable')
SELECT * FROM Produce

+-------------------------------------+
| item      | category   | purchases  |
+-------------------------------------+
| kale      | vegetable  | 23         |
| banana    | fruit      | 2          |
| cabbage   | vegetable  | 9          |
| apple     | fruit      | 8          |
| leek      | vegetable  | 2          |
| lettuce   | vegetable  | 10         |
+-------------------------------------+
``````

Employees table

Some examples reference a table called `Employees`:

``````WITH Employees AS
(SELECT 'Isabella' as name, 2 as department, DATE(1997, 09, 28) as start_date
UNION ALL SELECT 'Anthony', 1, DATE(1995, 11, 29)
UNION ALL SELECT 'Daniel', 2, DATE(2004, 06, 24)
UNION ALL SELECT 'Andrew', 1, DATE(1999, 01, 23)
UNION ALL SELECT 'Jacob', 1, DATE(1990, 07, 11)
UNION ALL SELECT 'Jose', 2, DATE(2013, 03, 17))
SELECT * FROM Employees

+-------------------------------------+
| name      | department | start_date |
+-------------------------------------+
| Isabella  | 2          | 1997-09-28 |
| Anthony   | 1          | 1995-11-29 |
| Daniel    | 2          | 2004-06-24 |
| Andrew    | 1          | 1999-01-23 |
| Jacob     | 1          | 1990-07-11 |
| Jose      | 2          | 2013-03-17 |
+-------------------------------------+
``````

Farm table

Some examples reference a table called `Farm`:

``````WITH Farm AS
(SELECT 'cat' as animal, 23 as population, 'mammal' as category
UNION ALL SELECT 'duck', 3, 'bird'
UNION ALL SELECT 'dog', 2, 'mammal'
UNION ALL SELECT 'goose', 1, 'bird'
UNION ALL SELECT 'ox', 2, 'mammal'
UNION ALL SELECT 'goat', 2, 'mammal')
SELECT * FROM Farm

+-------------------------------------+
| animal    | category   | population |
+-------------------------------------+
| cat       | mammal     | 23         |
| duck      | bird       | 3          |
| dog       | mammal     | 2          |
| goose     | bird       | 1          |
| ox        | mammal     | 2          |
| goat      | mammal     | 2          |
+-------------------------------------+
``````

Compute a grand total

This computes a grand total for all items in the `Produce` table.

• (banana, apple, leek, cabbage, lettuce, kale) = 54 total purchases
• (banana, apple, leek, cabbage, lettuce, kale) = 54 total purchases
• (banana, apple, leek, cabbage, lettuce, kale) = 54 total purchases
• (banana, apple, leek, cabbage, lettuce, kale) = 54 total purchases
• (banana, apple, leek, cabbage, lettuce, kale) = 54 total purchases
• (banana, apple, leek, cabbage, lettuce, kale) = 54 total purchases
``````SELECT item, purchases, category, SUM(purchases)
OVER () AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| banana    | 2          | fruit      | 54              |
| leek      | 2          | vegetable  | 54              |
| apple     | 8          | fruit      | 54              |
| cabbage   | 9          | vegetable  | 54              |
| lettuce   | 10         | vegetable  | 54              |
| kale      | 23         | vegetable  | 54              |
+-------------------------------------------------------+
``````

Compute a subtotal

This computes a subtotal for each category in the `Produce` table.

• fruit
• (banana, apple) = 10 total purchases
• (banana, apple) = 10 total purchases
• vegetable
• (leek, cabbage, lettuce, kale) = 44 total purchases
• (leek, cabbage, lettuce, kale) = 44 total purchases
• (leek, cabbage, lettuce, kale) = 44 total purchases
• (leek, cabbage, lettuce, kale) = 44 total purchases
``````SELECT item, purchases, category, SUM(purchases)
OVER (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| banana    | 2          | fruit      | 10              |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 44              |
| cabbage   | 9          | vegetable  | 44              |
| lettuce   | 10         | vegetable  | 44              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+
``````

Compute a cumulative sum

This computes a cumulative sum for each category in the `Produce` table. The sum is computed with respect to the order defined using the `ORDER BY` clause.

• fruit
• (banana, apple) = 2 total purchases
• (banana, apple) = 10 total purchases
• vegetable
• (leek, cabbage, lettuce, kale) = 2 total purchases
• (leek, cabbage, lettuce, kale) = 11 total purchases
• (leek, cabbage, lettuce, kale) = 21 total purchases
• (leek, cabbage, lettuce, kale) = 44 total purchases
``````SELECT item, purchases, category, SUM(purchases)
OVER (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS total_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| banana    | 2          | fruit      | 2               |
| apple     | 8          | fruit      | 10              |
| leek      | 2          | vegetable  | 2               |
| cabbage   | 9          | vegetable  | 11              |
| lettuce   | 10         | vegetable  | 21              |
| kale      | 23         | vegetable  | 44              |
+-------------------------------------------------------+
``````

This does the same thing as the preceding example. You don't have to add `CURRENT ROW` as a boundary unless you would like to for readability.

``````SELECT item, purchases, category, SUM(purchases)
OVER (
PARTITION BY category
ORDER BY purchases
ROWS UNBOUNDED PRECEDING
) AS total_purchases
FROM Produce
``````

In this example, all items in the `Produce` table are included in the partition. Only preceding rows are analyzed. The analysis starts two rows prior to the current row in the partition.

• (banana, leek, apple, cabbage, lettuce, kale) = NULL
• (banana, leek, apple, cabbage, lettuce, kale) = NULL
• (banana, leek, apple, cabbage, lettuce, kale) = 2
• (banana, leek, apple, cabbage, lettuce, kale) = 4
• (banana, leek, apple, cabbage, lettuce, kale) = 12
• (banana, leek, apple, cabbage, lettuce, kale) = 21
``````SELECT item, purchases, category, SUM(purchases)
OVER (
ORDER BY purchases
ROWS BETWEEN UNBOUNDED PRECEDING AND 2 PRECEDING
) AS total_purchases
FROM Produce;

+-------------------------------------------------------+
| item      | purchases  | category   | total_purchases |
+-------------------------------------------------------+
| banana    | 2          | fruit      | NULL            |
| leek      | 2          | vegetable  | NULL            |
| apple     | 8          | fruit      | 2               |
| cabbage   | 9          | vegetable  | 4               |
| lettuce   | 10         | vegetable  | 12              |
| kale      | 23         | vegetable  | 21              |
+-------------------------------------------------------+
``````

Compute a moving average

This computes a moving average in the `Produce` table. The lower boundary is 1 row before the current row. The upper boundary is 1 row after the current row.

• (banana, leek, apple, cabbage, lettuce, kale) = 2 average purchases
• (banana, leek, apple, cabbage, lettuce, kale) = 4 average purchases
• (banana, leek, apple, cabbage, lettuce, kale) = 6.3333 average purchases
• (banana, leek, apple, cabbage, lettuce, kale) = 9 average purchases
• (banana, leek, apple, cabbage, lettuce, kale) = 14 average purchases
• (banana, leek, apple, cabbage, lettuce, kale) = 16.5 average purchases
``````SELECT item, purchases, category, AVG(purchases)
OVER (
ORDER BY purchases
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS avg_purchases
FROM Produce

+-------------------------------------------------------+
| item      | purchases  | category   | avg_purchases   |
+-------------------------------------------------------+
| banana    | 2          | fruit      | 2               |
| leek      | 2          | vegetable  | 4               |
| apple     | 8          | fruit      | 6.33333         |
| cabbage   | 9          | vegetable  | 9               |
| lettuce   | 10         | vegetable  | 14              |
| kale      | 23         | vegetable  | 16.5            |
+-------------------------------------------------------+
``````

Compute the number of items within a range

This example gets the number of animals that have a similar population count in the `Farm` table.

• (goose, dog, ox, goat, duck, cat) = 4 animals between population range 0-2.
• (goose, dog, ox, goat, duck, cat) = 5 animals between population range 1-3.
• (goose, dog, ox, goat, duck, cat) = 5 animals between population range 1-3.
• (goose, dog, ox, goat, duck, cat) = 5 animals between population range 1-3.
• (goose, dog, ox, goat, duck, cat) = 4 animals between population range 2-4.
• (goose, dog, ox, goat, duck, cat) = 1 animal between population range 22-24.
``````SELECT animal, population, category, COUNT(*)
OVER (
ORDER BY population
RANGE BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS similar_population
FROM Farm;

+----------------------------------------------------------+
| animal    | population | category   | similar_population |
+----------------------------------------------------------+
| goose     | 1          | bird       | 4                  |
| dog       | 2          | mammal     | 5                  |
| ox        | 2          | mammal     | 5                  |
| goat      | 2          | mammal     | 5                  |
| duck      | 3          | bird       | 4                  |
| cat       | 23         | mammal     | 1                  |
+----------------------------------------------------------+
``````

This example gets the most popular item in each category. It defines how rows in a window are partitioned and ordered in each partition. The `Produce` table is referenced.

• fruit
• (banana, apple) = apple is most popular
• (banana, apple) = apple is most popular
• vegetable
• (leek, cabbage, lettuce, kale) = kale is most popular
• (leek, cabbage, lettuce, kale) = kale is most popular
• (leek, cabbage, lettuce, kale) = kale is most popular
• (leek, cabbage, lettuce, kale) = kale is most popular
``````SELECT item, purchases, category, LAST_VALUE(item)
OVER (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| banana    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | kale         |
| cabbage   | 9          | vegetable  | kale         |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+
``````

Get the last value in a range

This example gets the most popular item in a specific window frame, using the `Produce` table. The window frame analyzes up to three rows at a time. Take a close look at the `most_popular` column for vegetables. Instead of getting the most popular item in a specific category, it gets the most popular item in a specific range in that category.

• fruit
• (banana, apple) = apple is most popular
• (banana, apple) = apple is most popular
• vegetable
• (leek, cabbage, lettuce, kale) = cabbage is most popular
• (leek, cabbage, lettuce, kale) = lettuce is most popular
• (leek, cabbage, lettuce, kale) = kale is most popular
• (leek, cabbage, lettuce, kale) = kale is most popular
``````SELECT item, purchases, category, LAST_VALUE(item)
OVER (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS most_popular
FROM Produce

+----------------------------------------------------+
| item      | purchases  | category   | most_popular |
+----------------------------------------------------+
| banana    | 2          | fruit      | apple        |
| apple     | 8          | fruit      | apple        |
| leek      | 2          | vegetable  | cabbage      |
| cabbage   | 9          | vegetable  | lettuce      |
| lettuce   | 10         | vegetable  | kale         |
| kale      | 23         | vegetable  | kale         |
+----------------------------------------------------+
``````

This example returns the same results as the preceding example, but it includes a named window called `item_window`. Some of the window specifications are defined directly in the `OVER` clause and some are defined in the named window.

``````SELECT item, purchases, category, LAST_VALUE(item)
OVER (
item_window
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS most_popular
FROM Produce
WINDOW item_window AS (
PARTITION BY category
ORDER BY purchases)
``````

Compute rank

This example calculates the rank of each employee within their department, based on their start date. The window specification is defined directly in the `OVER` clause. The `Employees` table is referenced.

• department 1
• (Jacob, Anthony, Andrew) = Assign rank 1 to Jacob
• (Jacob, Anthony, Andrew) = Assign rank 2 to Anthony
• (Jacob, Anthony, Andrew) = Assign rank 3 to Andrew
• department 2
• (Isabella, Daniel, Jose) = Assign rank 1 to Isabella
• (Isabella, Daniel, Jose) = Assign rank 2 to Daniel
• (Isabella, Daniel, Jose) = Assign rank 3 to Jose
``````SELECT name, department, start_date,
RANK() OVER (PARTITION BY department ORDER BY start_date) AS rank
FROM Employees;

+--------------------------------------------+
| name      | department | start_date | rank |
+--------------------------------------------+
| Jacob     | 1          | 1990-07-11 | 1    |
| Anthony   | 1          | 1995-11-29 | 2    |
| Andrew    | 1          | 1999-01-23 | 3    |
| Isabella  | 2          | 1997-09-28 | 1    |
| Daniel    | 2          | 2004-06-24 | 2    |
| Jose      | 2          | 2013-03-17 | 3    |
+--------------------------------------------+
``````

Use a named window in a window frame clause

You can define some of your logic in a named window and some of it in a window frame clause. This logic is combined. Here is an example, using the `Produce` table.

``````SELECT item, purchases, category, LAST_VALUE(item)
OVER (item_window) AS most_popular
FROM Produce
WINDOW item_window AS (
PARTITION BY category
ORDER BY purchases
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

+-------------------------------------------------------+
| item      | purchases  | category   | most_popular    |
+-------------------------------------------------------+
| banana    | 2          | fruit      | apple           |
| apple     | 8          | fruit      | apple           |
| leek      | 2          | vegetable  | lettuce         |
| cabbage   | 9          | vegetable  | kale            |
| lettuce   | 10         | vegetable  | kale            |
| kale      | 23         | vegetable  | kale            |
+-------------------------------------------------------+
``````

You can also get the previous results with these examples:

``````SELECT item, purchases, category, LAST_VALUE(item)
OVER (item_window) AS most_popular
FROM Produce
WINDOW
a AS (PARTITION BY category),
b AS (a ORDER BY purchases),
c AS (b ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING),
item_window AS (c)
``````
``````SELECT item, purchases, category, LAST_VALUE(item)
OVER (item_window ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS most_popular
FROM Produce
WINDOW
a AS (PARTITION BY category),
b AS (a ORDER BY purchases),
item_window AS (b)
``````

The following example produces an error because a window frame clause has been defined twice:

``````SELECT item, purchases, category, LAST_VALUE(item)
OVER (
item_window
ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING
) AS most_popular
FROM Produce
WINDOW item_window AS (
ORDER BY purchases
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)
``````
[{ "type": "thumb-down", "id": "hardToUnderstand", "label":"Hard to understand" },{ "type": "thumb-down", "id": "incorrectInformationOrSampleCode", "label":"Incorrect information or sample code" },{ "type": "thumb-down", "id": "missingTheInformationSamplesINeed", "label":"Missing the information/samples I need" },{ "type": "thumb-down", "id": "otherDown", "label":"Other" }]
[{ "type": "thumb-up", "id": "easyToUnderstand", "label":"Easy to understand" },{ "type": "thumb-up", "id": "solvedMyProblem", "label":"Solved my problem" },{ "type": "thumb-up", "id": "otherUp", "label":"Other" }]