ResourcesCommunityStatusLogin

Window Functions

ROW_NUMBER

ROW_NUMBER() Number of the current row within its partition, counting from 1.

WITH examples AS (
      SELECT 'John' AS name
      UNION ALL SELECT 'Jane'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'James'
  )
  SELECT ROW_NUMBER() OVER()
  FROM examples
+--------------+ | row_number | |--------------+ | 1 | | 2 | | 3 | | 4 | +--------------+

RANK

RANK() Rank of the current row with gaps; same as row_number of its first peer.

WITH examples AS (
      SELECT 'John' AS name
      UNION ALL SELECT 'Jane'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'James'
  )
  SELECT name, RANK() OVER(ORDER BY name)
  FROM examples
+-----------+------+ | name | rank | |-----------+------| | John | 4 | | Jane | 3 | | Angela | 1 | | James | 2 | +-----------+------+

💡

Note on order of results with ORDER BY

ORDER BY inside of a window function may not affect the row ordering in the output columns. The only thing ORDER BY guarantees is the order in which the window function is applied across a partition. See our documentation on the Over function here for more information.

DENSE_RANK

DENSE_RANK() Rank of the current row without gaps; this function counts peer groups.

WITH examples AS (
      SELECT 'John' AS name
      UNION ALL SELECT 'Jane'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'James'
  )
  SELECT name, DENSE_RANK() OVER(ORDER BY name)
  FROM examples
+-----------+------------+ | name | dense_rank | |-----------+------------| | John | 4 | | Jane | 3 | | Angela | 1 | | Angela | 1 | | James | 2 | +-----------+------------+

PERCENT_RANK

PERCENT_RANK() Relative rank of the current row: (rank - 1) / (total rows - 1).

WITH examples AS (
      SELECT 'John' AS name
      UNION ALL SELECT 'Jane'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'James'
  )
  SELECT name, PERCENT_RANK() OVER(ORDER BY name)
  FROM examples
+-----------+--------------+ | name | percent_rank | |-----------+--------------| | John | 1 | | Jane | 0.75 | | Angela | 0 | | Angela | 0 | | James | 0.5 | +-----------+--------------+

CUME_DIST

CUME_DIST() Relative rank of the current row: (number of rows preceding or peer with current row) / (total rows).

WITH examples AS (
      SELECT 'John' AS name
      UNION ALL SELECT 'Jane'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'James'
  )
  SELECT name, CUME_DIST() OVER(ORDER BY name)
  FROM examples
+-----------+------------+ | name | cume_dist | |-----------+------------| | John | 1 | | Jane | 0.8 | | Angela | 0.4 | | Angela | 0.4 | | James | 0.6 | +-----------+------------+

NTILE

NTILE(num_buckets) Integer ranging from 1 to the argument value, dividing the partition as equally as possible.

WITH examples AS (
      SELECT 'John' AS name
      UNION ALL SELECT 'Jane'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'James'
  )
  SELECT name, NTILE(2) OVER(ORDER BY name)
  FROM examples
+-----------+-------+ | name | ntile | |-----------+-------| | John | 2 | | Jane | 2 | | Angela | 1 | | Angela | 1 | | James | 1 | +-----------+-------+

LAG

LAG(value [, offset [, default ]]) Returns value evaluated at the row that is offset rows before the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null.

WITH examples AS (
      SELECT 'John' AS name
      UNION ALL SELECT 'Jane'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'James'
  )
  SELECT name, LAG(name, 2) OVER(ORDER BY name)
  FROM examples
+-----------+------------+ | name | lag | |-----------+------------| | John | James | | Jane | Angela | | Angela | null | | Angela | null | | James | Angela | +-----------+------------+

LEAD

LEAD(value [, offset [, default ]]) Returns value evaluated at the row that is offset rows after the current row within the partition; if there is no such row, instead return default. Both offset and default are evaluated with respect to the current row. If omitted, offset defaults to 1 and default to null.

WITH examples AS (
      SELECT 'John' AS name
      UNION ALL SELECT 'Jane'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'James'
  )
  SELECT name, LEAD(name, 2) OVER(ORDER BY name)
  FROM examples
+-----------+------------+ | name | lead | |-----------+------------| | John | null | | Jane | null | | Angela | James | | Angela | Jane | | James | John | +-----------+------------+

FIRST_VALUE

FIRST_VALUE(value) Returns value evaluated at the row that is the first row of the window frame.

WITH examples AS (
      SELECT 'John' AS name
      UNION ALL SELECT 'Jane'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'James'
  )
  SELECT name, FIRST_VALUE(name) OVER(ORDER BY name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  FROM examples
+-----------+-------------+ | name | last_value | |-----------+-------------| | John | Jane | | Jane | James | | Angela | Angela | | Angela | Angela | | James | Angela | +-----------+-------------+

LAST_VALUE

LAST_VALUE(value) Returns value evaluated at the row that is the last row of the window frame.

WITH examples AS (
      SELECT 'John' AS name
      UNION ALL SELECT 'Jane'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'James'
  )
  SELECT name, LAST_VALUE(name) OVER(ORDER BY name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  FROM examples
+-----------+-------------+ | name | last_value | |-----------+-------------| | John | John | | Jane | John | | Angela | Angela | | Angela | James | | James | Jane | +-----------+-------------+

NTH_VALUE

NTH_VALUE(value, nth) Returns value evaluated at the row that is the nth row of the window frame (counting from 1); null if no such row.

WITH examples AS (
      SELECT 'John' AS name
      UNION ALL SELECT 'Jane'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'Angela'
      UNION ALL SELECT 'James'
  )
  SELECT name, NTH_VALUE(name, 2) OVER(ORDER BY name ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)
  FROM examples
+-----------+-------------+ | name | first_value | |-----------+-------------| | John | John | | Jane | Jane | | Angela | Angela | | Angela | Angela | | James | James | +-----------+-------------+

All of the functions listed above depend on the sort ordering specified by the ORDER BY clause of the associated window definition. Rows that are not distinct in the ORDER BY ordering are peers; the four ranking functions are defined so that they give the same answer for any two peers.

Note that FIRST_VALUE, LAST_VALUE, and NTH_VALUE consider only the rows within the window frame.