Window functions
Topics
 Window function syntax summary
 AVG window function
 COUNT window function
 CUME_DIST window function
 DENSE_RANK window function
 FIRST_VALUE and LAST_VALUE window functions
 LAG window function
 LEAD window function
 LISTAGG window function
 MAX window function
 MEDIAN window function
 MIN window function
 NTH_VALUE window function
 NTILE window function
 PERCENT_RANK window function
 PERCENTILE_CONT window function
 PERCENTILE_DISC window function
 RANK window function
 RATIO_TO_REPORT window function
 ROW_NUMBER window function
 STDDEV_SAMP and STDDEV_POP window functions
 SUM window function
 VAR_SAMP and VAR_POP window functions
 Window function examples
Window functions provide application developers the ability to create analytic business queries more efficiently. Window functions operate on a partition or "window" of a result set, and return a value for every row in that window. In contrast, nonwindowed functions perform their calculations with respect to every row in the result set. Unlike group functions that aggregate result rows, all rows in the table expression are retained.
The values returned are calculated by utilizing values from the sets of rows in that window. The window defines, for each row in the table, a set of rows that is used to compute additional attributes. A window is defined using a window specification (the OVER clause), and is based on three main concepts:

Window partitioning, which forms groups of rows (PARTITION clause)

Window ordering, which defines an order or sequence of rows within each partition (ORDER BY clause)

Window frames, which are defined relative to each row to further restrict the set of rows (ROWS specification)
Window functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the window functions are processed. Therefore, window functions can appear only in the select list or ORDER BY clause. Multiple window functions can be used within a single query with different frame clauses. Window functions may be present in other scalar expressions, such as CASE.
Amazon Redshift supports two types of window functions: aggregate and ranking.
These are the supported aggregate functions:

AVG

COUNT

CUME_DIST

FIRST_VALUE

LAG

LAST_VALUE

LEAD

MAX

MEDIAN

MIN

NTH_VALUE

PERCENTILE_CONT

PERCENTILE_DISC

RATIO_TO_REPORT

STDDEV_POP

STDDEV_SAMP (synonym for STDDEV)

SUM

VAR_POP

VAR_SAMP (synonym for VARIANCE)
These are the supported ranking functions:

DENSE_RANK

NTILE

PERCENT_RANK

RANK

ROW_NUMBER