Window Functions

row_number

row_number() → BIGINT

Position of the row within its window, counting from 1. Ties are numbered arbitrarily — use rank if equal values should share a number.

Sample query:

// row_number: number each customer's orders from their earliest, without collapsing any rows.
FIND customers c, orders o
FILTER c.country = 'France'
FETCH c.customer_id, o.order_id, row_number() OVER (PARTITION c.customer_id ORDER o.order_id) nth

Generated SQL

all dialects

-- row_number: number each customer's orders from their earliest, without collapsing any rows.
SELECT
  c.customer_id
, o.order_id
, row_number() OVER (PARTITION BY c.customer_id ORDER BY o.order_id) AS nth
FROM
 customers c
  INNER JOIN orders o ON
   c.customer_id = o.customer_id
WHERE
  c.country = 'France'

rank

rank() → BIGINT

Position of the row by the window's ordering, where equal values share a rank and the next rank skips ahead: 1, 2, 2, 4.

Sample query:

// rank: position by freight, heaviest first. Equal freights share a rank and the next one skips.
FIND orders o
FILTER o.ship_country = 'France'
FETCH o.order_id, o.freight, rank() OVER (ORDER o.freight DESC) freight_rank

Generated SQL

all dialects

-- rank: position by freight, heaviest first. Equal freights share a rank and the next one skips.
SELECT
  o.order_id
, o.freight
, rank() OVER ( ORDER BY o.freight DESC) AS freight_rank
FROM
 orders o
WHERE
  o.ship_country = 'France'

dense_rank

dense_rank() → BIGINT

Like rank, but without gaps after a tie: 1, 2, 2, 3.

Sample query:

// dense_rank: like rank, but the numbering has no gaps after a tie.
FIND orders o
FILTER o.ship_country = 'France'
FETCH o.order_id, o.freight, dense_rank() OVER (ORDER o.freight DESC) freight_rank

Generated SQL

all dialects

-- dense_rank: like rank, but the numbering has no gaps after a tie.
SELECT
  o.order_id
, o.freight
, dense_rank() OVER ( ORDER BY o.freight DESC) AS freight_rank
FROM
 orders o
WHERE
  o.ship_country = 'France'

ntile

ntile(buckets: numeric) → BIGINT

Splits the window's rows into buckets groups of near-equal size and returns which group the row falls in — quartiles with ntile(4).

Argument	Type	Description
buckets	numeric	how many groups to divide the rows into

Sample query:

// ntile: split the orders into four groups of near-equal size by freight — quartiles.
FIND orders o
FILTER o.ship_country = 'France'
FETCH o.order_id, o.freight, ntile(4) OVER (ORDER o.freight) quartile

Generated SQL

all dialects

-- ntile: split the orders into four groups of near-equal size by freight — quartiles.
SELECT
  o.order_id
, o.freight
, ntile(4) OVER ( ORDER BY o.freight) AS quartile
FROM
 orders o
WHERE
  o.ship_country = 'France'

lag

lag(value: any [, offset: numeric] [, default: any]) → argument-dependent

The value from an earlier row of the window — the previous month's figure, say, for comparing against this one.

Argument	Type	Description
value	any	the value to read from an earlier row
offset	numeric (optional)	how many rows back to look; 1 (the previous row) by default
default	any (optional)	value to use when there is no such row; null by default

Sample query:

// lag: each order alongside the previous order's freight for the same customer — the shape behind
// "compare with the period before".
FIND customers c, orders o
FILTER c.country = 'France'
FETCH c.customer_id, o.order_id, o.freight, lag(o.freight) OVER (PARTITION c.customer_id ORDER o.order_id) previous_freight

Generated SQL

all dialects

-- lag: each order alongside the previous order's freight for the same customer — the shape behind
-- "compare with the period before".
SELECT
  c.customer_id
, o.order_id
, o.freight
, lag(o.freight) OVER (PARTITION BY c.customer_id ORDER BY o.order_id) AS previous_freight
FROM
 customers c
  INNER JOIN orders o ON
   c.customer_id = o.customer_id
WHERE
  c.country = 'France'

lead

lead(value: any [, offset: numeric] [, default: any]) → argument-dependent

The value from a later row of the window — the mirror of lag.

Argument	Type	Description
value	any	the value to read from a later row
offset	numeric (optional)	how many rows forward to look; 1 (the next row) by default
default	any (optional)	value to use when there is no such row; null by default

Sample query:

// lead: the mirror of lag — the next order's freight for the same customer.
FIND customers c, orders o
FILTER c.country = 'France'
FETCH c.customer_id, o.order_id, o.freight, lead(o.freight) OVER (PARTITION c.customer_id ORDER o.order_id) next_freight

Generated SQL

all dialects

-- lead: the mirror of lag — the next order's freight for the same customer.
SELECT
  c.customer_id
, o.order_id
, o.freight
, lead(o.freight) OVER (PARTITION BY c.customer_id ORDER BY o.order_id) AS next_freight
FROM
 customers c
  INNER JOIN orders o ON
   c.customer_id = o.customer_id
WHERE
  c.country = 'France'