A window function, also known as an analytic function, computes valuesover a group of rows and returns a single result foreach row. This isdifferent from an aggregate function, which returns a single result fora group of rows.

A window function includes anOVER clause, which defines a window of rowsaround the row being evaluated. For each row, the window function resultis computed using the selected window of rows as input, possiblydoing aggregation.

With window functions you can compute moving averages, rank items, calculatecumulative sums, and perform other analyses.

Window function syntax

function_name ( [ argument_list ] ) OVER over_clauseover_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 }

Description

A window function computes results over a group of rows. You can use thefollowing syntax to build a window function:

• function_name: The function that performs a window operation.

  For example, the numbering functionRANK() could be used here.

• argument_list: Arguments that are specific to the function.Some functions have them, some don't.

• OVER: Keyword required in the window function syntax precedingtheOVER clause.

• over_clause: References a window that defines a groupof rows in a table upon which to use a window function.

• window_specification: Defines the specifications forthe window.

• window_frame_clause: Defines the window framefor the window.

• rows_range: Defines the physical rows or alogical range for a window frame.

Notes

A window function can appear as a scalar expression operand inthe following places in the query:

• TheSELECT list. If the window function appears in theSELECT list,its argument list andOVER clause can't refer to aliases introducedin the sameSELECT list.
• TheORDER BY clause. If the window function appears in theORDER BYclause of the query, its argument list can refer toSELECTlist aliases.
• TheQUALIFY clause.

A window function can't refer to another window function in itsargument list or itsOVER clause, even indirectly through an alias.

A window function is evaluated after aggregation. For example, theGROUP BY clause and non-window aggregate functions are evaluated first.Because aggregate functions are evaluated before window functions,aggregate functions can be used as input operands to window functions.

Returns

A single result for each row in the input.

Defining theOVER clause

function_name([argument_list])OVERover_clauseover_clause:{named_window|([window_specification])}

Description

TheOVER clause references a window that defines a group of rows in a tableupon which to use a window function. You can provide anamed_window that isdefined in your query, or you candefine thespecifications for a new window.

Notes

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

Examples using theOVER clause

These queries use window specifications:

• Compute a grand total
• Compute a subtotal
• Compute a cumulative sum
• Compute a moving average
• Compute the number of items within a range
• Get the most popular item in each category
• Get the last value in a range
• Compute rank

These queries use a named window:

• Get the last value in a range
• Use a named window in a window frame clause

Defining the window specification

window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]

Description

Defines the specifications for the window.

• named_window: The name of an existing window that wasdefined with aWINDOW clause.

• PARTITION BY: Breaks up the input rows into separate partitions, overwhich the window function is independently evaluated.
  • Apartition_expression computes a value that determines which partitioneach row falls into.
  • Multiple partition expressions are allowed in thePARTITION BY clause.
  • An expression can't contain floating point types, non-groupable types,constants, or window functions.
  • If this optional clause isn't used, all rows in the input tablecomprise a single partition.

• ORDER BY: Defines how rows are ordered within a partition.

  This clause is optional in most situations, but is required in somecases fornavigation functions.

• window_frame_clause: For aggregate analyticfunctions, 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 fullyunbounded windows.

Notes

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

For aggregate analytic functions, if theORDER BY clause is present butthe window frame clause isn't, the following window frame clause isused by default:

RANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW

For example, the following queries are equivalent:

SELECTbook,LAST_VALUE(book)OVER(ORDERBYyear)FROMLibrary

SELECTbook,LAST_VALUE(book)OVER(ORDERBYyearRANGEBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)FROMLibrary

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 extendedwith new specifications that you define in the window specification clause.
• You can't have redundant definitions. If you have anORDER BY clausein the named window and the window specification clause, anerror is thrown.

• The order of clauses matters.PARTITION BY must come first,followed byORDER BY andwindow_frame_clause. If you add a named window,its window specifications are processed first.

  --thisworks:SELECTitem,purchases,LAST_VALUE(item)OVER(ItemWindowROWSBETWEEN2PRECEDINGAND2FOLLOWING)ASmost_popularFROMProduceWINDOWItemWindowAS(ORDERBYpurchases)--thisdoesn't work:SELECT item, purchases, LAST_VALUE(item)  OVER (ItemWindow ORDER BY purchases) AS most_popularFROM ProduceWINDOW ItemWindow AS (ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING)

• A named window andPARTITION BY can't appear together in thewindow specification. If you needPARTITION BY, add it to the named window.

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

Examples using the window specification

These queries define partitions in a window function:

• Compute a subtotal
• Compute a cumulative sum
• Get the most popular item in each category
• Get the last value in a range
• Compute rank
• Use a named window in a window frame clause

These queries include a named window in a window specification:

• Get the last value in a range
• Use a named window in a window frame clause

These queries define how rows are ordered in a partition:

• Compute a subtotal
• Compute a cumulative sum
• Compute a moving average
• Compute the number of items within a range
• Get the most popular item in each category
• Get the last value in a range
• Compute rank
• Use a named window in a window frame clause

Defining the window frame clause

window_frame_clause:{rows_range}{frame_start|frame_between}rows_range:{ROWS|RANGE}frame_between:{BETWEENunbounded_precedingANDframe_end_a|BETWEENnumeric_precedingANDframe_end_a|BETWEENcurrent_rowANDframe_end_b|BETWEENnumeric_followingANDframe_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:UNBOUNDEDPRECEDINGnumeric_preceding:numeric_expressionPRECEDINGunbounded_following:UNBOUNDEDFOLLOWINGnumeric_following:numeric_expressionFOLLOWINGcurrent_row:CURRENTROW

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

• rows_range: A clause that defines a window frame with physical rowsor a logical range.

  • ROWS: Computes the window frame based on physical offsets from thecurrent row. For example, you could include two rows before and afterthe current row.
  • RANGE: Computes the window frame based on a logical range of rowsaround the current row, based on the current row’sORDER BY key value.The provided range value is added or subtracted to the current row'skey value to define a starting or ending range boundary for thewindow frame. In a range-based window frame, there must be exactly oneexpression in theORDER BY clause, and the expression must have anumeric type.
  Tip: If you want to use a range with a date, useORDER BY with theUNIX_DATE() function. If you want to use a range with a timestamp,use theUNIX_SECONDS(),UNIX_MILLIS(), orUNIX_MICROS() function.

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

  • Define the beginning of the window frame withunbounded_preceding,numeric_preceding,numeric_following, orcurrent_row.
    • unbounded_preceding: The window frame starts at the beginning of thepartition.
    • numeric_preceding ornumeric_following: The start of the windowframe is relative to thecurrent row.
    • current_row: The window frame starts at the current row.
  • Define the end of the window frame withnumeric_preceding,numeric_following,current_row, orunbounded_following.
    • numeric_preceding ornumeric_following: The end of the windowframe 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 thepartition.

• 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 thepartition.
  • numeric_preceding: The start of the window frame is relative to thecurrent 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 integeror 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 somenavigation functions andnumbering functions,such asRANK().

Examples using the window frame clause

These queries compute values withROWS:

• Compute a cumulative sum
• Compute a moving average
• Get the most popular item in each category
• Get the last value in a range
• Use a named window in a window frame clause

These queries compute values withRANGE:

• Compute the number of items within a range

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

• Compute a grand total
• Compute a subtotal
• Compute a cumulative sum
• Get the most popular item in each category
• Compute rank

These queries compute values with numeric boundaries:

• Compute a cumulative sum
• Compute a moving average
• Compute the number of items within a range
• Get the last value in a range
• Use a named window in a window frame clause

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

• Compute a grand total
• Compute a subtotal
• Compute a cumulative sum

Referencing a named window

SELECTquery_expr,function_name([argument_list])OVERover_clauseFROMfrom_itemWINDOWnamed_window_expression[,...]over_clause:{named_window|([window_specification])}window_specification:[named_window][PARTITIONBYpartition_expression[,...]][ORDERBYexpression[{ASC|DESC}][,...]][window_frame_clause]named_window_expression:named_windowAS{named_window|([window_specification])}

A named window represents a group of rows in a table upon which to use anwindow function. A named window is defined in theWINDOW clause, and referenced ina window function'sOVER clause.In anOVER clause, a named window can appear either by itself or embeddedwithin awindow specification.

Examples

• Get the last value in a range
• Use a named window in a window frame clause

Filtering results with the QUALIFY clause

TheQUALIFY clause can be used to filter the results of a window function.For more information and examples, see theQUALIFY clause.

Window function examples

In these examples, thehighlighted item is the current row. Theboldeditems are the rows that are included in the analysis.

Common tables used in examples

The following tables are used in the subsequent aggregate analyticquery examples:Produce,Employees,andFarm.

Produce table

Some examples reference a table calledProduce:

WITHProduceAS(SELECT'kale'asitem,23aspurchases,'vegetable'ascategoryUNIONALLSELECT'banana',2,'fruit'UNIONALLSELECT'cabbage',9,'vegetable'UNIONALLSELECT'apple',8,'fruit'UNIONALLSELECT'leek',2,'vegetable'UNIONALLSELECT'lettuce',10,'vegetable')SELECT*FROMProduce/*-------------------------------------+ | 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 calledEmployees:

WITHEmployeesAS(SELECT'Isabella'asname,2asdepartment,DATE(1997,09,28)asstart_dateUNIONALLSELECT'Anthony',1,DATE(1995,11,29)UNIONALLSELECT'Daniel',2,DATE(2004,06,24)UNIONALLSELECT'Andrew',1,DATE(1999,01,23)UNIONALLSELECT'Jacob',1,DATE(1990,07,11)UNIONALLSELECT'Jose',2,DATE(2013,03,17))SELECT*FROMEmployees/*-------------------------------------+ | 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 calledFarm:

WITHFarmAS(SELECT'cat'asanimal,23aspopulation,'mammal'ascategoryUNIONALLSELECT'duck',3,'bird'UNIONALLSELECT'dog',2,'mammal'UNIONALLSELECT'goose',1,'bird'UNIONALLSELECT'ox',2,'mammal'UNIONALLSELECT'goat',2,'mammal')SELECT*FROMFarm/*-------------------------------------+ | 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 theProduce 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

SELECTitem,purchases,category,SUM(purchases)OVER()AStotal_purchasesFROMProduce/*-------------------------------------------------------+ | 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 theProduce 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

SELECTitem,purchases,category,SUM(purchases)OVER(PARTITIONBYcategoryORDERBYpurchasesROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)AStotal_purchasesFROMProduce/*-------------------------------------------------------+ | 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 theProduce table. The sum is computed with respect to theorder defined using theORDER 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

SELECTitem,purchases,category,SUM(purchases)OVER(PARTITIONBYcategoryORDERBYpurchasesROWSBETWEENUNBOUNDEDPRECEDINGANDCURRENTROW)AStotal_purchasesFROMProduce/*-------------------------------------------------------+ | 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 addCURRENT ROW as a boundary unless you would like to for readability.

SELECTitem,purchases,category,SUM(purchases)OVER(PARTITIONBYcategoryORDERBYpurchasesROWSUNBOUNDEDPRECEDING)AStotal_purchasesFROMProduce

In this example, all items in theProduce table are includedin the partition. Only preceding rows are analyzed. The analysis starts tworows 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

SELECTitem,purchases,category,SUM(purchases)OVER(ORDERBYpurchasesROWSBETWEENUNBOUNDEDPRECEDINGAND2PRECEDING)AStotal_purchasesFROMProduce;/*-------------------------------------------------------+ | 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 theProduce table.The lower boundary is 1 row before thecurrent 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

SELECTitem,purchases,category,AVG(purchases)OVER(ORDERBYpurchasesROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASavg_purchasesFROMProduce/*-------------------------------------------------------+ | 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 populationcount in theFarm 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.

SELECTanimal,population,category,COUNT(*)OVER(ORDERBYpopulationRANGEBETWEEN1PRECEDINGAND1FOLLOWING)ASsimilar_populationFROMFarm;/*----------------------------------------------------------+ | 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                  | +----------------------------------------------------------*/

Get the most popular item in each category

This example gets the most popular item in each category. It defines how rowsin a window are partitioned and ordered in each partition. TheProduce 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

SELECTitem,purchases,category,LAST_VALUE(item)OVER(PARTITIONBYcategoryORDERBYpurchasesROWSBETWEENUNBOUNDEDPRECEDINGANDUNBOUNDEDFOLLOWING)ASmost_popularFROMProduce/*----------------------------------------------------+ | 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, usingtheProduce table. The window frame analyzes up to threerows at a time. Take a close look at themost_popular column for vegetables.Instead of getting the most popular item in a specific category, it gets themost 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

SELECTitem,purchases,category,LAST_VALUE(item)OVER(PARTITIONBYcategoryORDERBYpurchasesROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASmost_popularFROMProduce/*----------------------------------------------------+ | 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 includesa named window calledItemWindow. Some of the window specifications aredefined directly in theOVER clause and some are defined in the named window.

SELECTitem,purchases,category,LAST_VALUE(item)OVER(ItemWindowROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASmost_popularFROMProduceWINDOWItemWindowAS(PARTITIONBYcategoryORDERBYpurchases)

Compute rank

This example calculates the rank of each employee within their department,based on their start date. The window specification is defined directlyin theOVER clause. TheEmployees 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

SELECTname,department,start_date,RANK()OVER(PARTITIONBYdepartmentORDERBYstart_date)ASrankFROMEmployees;/*--------------------------------------------+ | 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 awindow frame clause. This logic is combined. Here is an example, using theProduce table.

SELECTitem,purchases,category,LAST_VALUE(item)OVER(ItemWindow)ASmost_popularFROMProduceWINDOWItemWindowAS(PARTITIONBYcategoryORDERBYpurchasesROWSBETWEEN2PRECEDINGAND2FOLLOWING)/*-------------------------------------------------------+ | 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:

SELECTitem,purchases,category,LAST_VALUE(item)OVER(ItemWindow)ASmost_popularFROMProduceWINDOWaAS(PARTITIONBYcategory),bAS(aORDERBYpurchases),cAS(bROWSBETWEEN2PRECEDINGAND2FOLLOWING),ItemWindowAS(c)

SELECTitem,purchases,category,LAST_VALUE(item)OVER(ItemWindowROWSBETWEEN2PRECEDINGAND2FOLLOWING)ASmost_popularFROMProduceWINDOWaAS(PARTITIONBYcategory),bAS(aORDERBYpurchases),ItemWindowAS(b)

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

SELECTitem,purchases,category,LAST_VALUE(item)OVER(ItemWindowROWSBETWEEN1PRECEDINGAND1FOLLOWING)ASmost_popularFROMProduceWINDOWItemWindowAS(ORDERBYpurchasesROWSBETWEEN2PRECEDINGAND2FOLLOWING)