Window functions - e6data Docs

ReferenceApplies to: Unified engine deployments

Window functions require OVER to define the relevant rows. Their ordering is independent of the query’s final presentation order. See window queries for frame syntax.

Function	Returns	Behavior
`row_number()`	Integer	Unique sequence within a partition
`rank()`	Integer	Rank with gaps after ties
`dense_rank()`	Integer	Rank without gaps after ties
`ntile(buckets)`	Integer	Assign ordered rows to approximately equal buckets
`lag(value[, offset[, default]])`	Value-compatible type	Earlier value in the partition
`lead(value[, offset[, default]])`	Value-compatible type	Later value in the partition
`first_value(value)`	Value-compatible type	First value in the frame
`last_value(value)`	Value-compatible type	Last value in the frame
`nth_value(value, position)`	Value-compatible type	Value at a one-based position in the frame

Ranking and buckets

RANK and DENSE_RANK treat equal ordering values as peers. ROW_NUMBER needs a tie-breaker for deterministic positions.

SELECT i_item_sk, i_current_price,
       RANK() OVER (ORDER BY i_current_price DESC NULLS LAST) AS price_rank,
       DENSE_RANK() OVER (ORDER BY i_current_price DESC NULLS LAST) AS dense_price_rank,
       NTILE(4) OVER (ORDER BY i_item_sk) AS item_quartile
FROM demo.tpcds.item
WHERE i_item_sk <= 20
ORDER BY i_item_sk;

Frame-sensitive value functions

LAST_VALUE returns the last value of its frame, which is not necessarily the last row of the whole partition. State the frame explicitly when you want a whole-partition result.

SELECT i_item_sk, i_current_price,
       FIRST_VALUE(i_current_price) OVER (
         ORDER BY i_item_sk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS first_price,
       LAST_VALUE(i_current_price) OVER (
         ORDER BY i_item_sk ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_price
FROM demo.tpcds.item
WHERE i_item_sk <= 5
ORDER BY i_item_sk;

LAG or LEAD outside a partition returns its supplied default, otherwise NULL. Explicit IGNORE NULLS and RESPECT NULLS clauses are currently rejected by Native compilation. A window result cannot be filtered with QUALIFY; wrap it in a subquery and filter outside it.

Last modified on November 4, 2026