# Window Functions

Window functions return a value for each row they operate on. A window is defined using the `OVER()` clause. Window functions use the rows defined in this window when returning results.

`OVER()` clause can take the following optional arguments:

• `PARTITION BY`: To define multiple window partitions.
• `ORDER BY`: To order rows within each partition.

Syntax

``````window_function (expression) OVER (
[ PARTITION BY expressionlist ]
[ ORDER BY fieldlist ] )
``````

### Aggregate Functions

`OVER()` clause can be used with regular aggregate functions such as:

• AVG
• COUNT
• MAX
• MIN
• SUM

Example

``````select
product_id,
branch,
amount,
SUM(amount) OVER (partition by branch order by amount DESC) as total_branch_amount
from transactions
``````
product_id branch amount total_branch_amount
Product1 A 30 56
Product2 A 24 56
Product3 A 2 56
Product3 B 45 58
Product2 B 10 58
Product1 B 3 58

### Analytic Functions

`OVER()` clause can also be used with specific window functions.

Function Return Type Description
CUME_DIST() Double Calculates the cumulative distribution of the current row within the window partition.
DENSE_RANK() BIGINT Returns the rank of the current row within it's partition and ordering. Rows that are equal will have the same rank.
LAG() Same as input Returns the row before the current one in a partition. If there are no rows returns null.
LEAD() Same as input Returns the row after the current one in a partition. If there are no rows returns null.
NTILE([integer] ntile) Integer NTILE function equally splits the rows in each partition into N ranked parts. Has to be used with order by clause.
PERCENT_RANK() Double Returns the percent rankf of the current row in the partition based on the order by clause.
RANK() BIGINT Returns the rank of the current row within it's partition and ordering. Rows that are equal will have the same rank. However, the count of tied rows will be added to the next rank, instead of incrementing by just one.
ROW_NUMBER() BIGINT Returns the row number for the current row based on the order by clause within each partition.