> ## Documentation Index
> Fetch the complete documentation index at: https://docs.e6data.com/llms.txt
> Use this file to discover all available pages before exploring further.

# Window functions

> Window functions require OVER to define the relevant rows. Their ordering is independent of the query's final presentation order. See window queries(windows.md) for frame syntax.

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](https://docs.e6data.com/transformation/sql/windows.md) 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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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.

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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.


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.