Generate an array of values from start to stop with a given step.
SEQUENCE(start, stop [, step])## Overview Returns an array of values ranging from start to stop (inclusive) incremented by step. SEQUENCE is the most direct way to generate an inline range of integers, dates, or timestamps without a dedicated row source, and it pairs naturally with EXPLODE or TRANSFORM to produce row-by-row work. Typical uses include building date spines for gap filling, generating index vectors for TRANSFORM, producing test data inline, and turning a numeric range into a set of rows. ## Behavior - Returns an ARRAY<T> where T matches the argument type (INT, BIGINT, DATE, or TIMESTAMP). - Both endpoints are inclusive. The generated array contains all values v such that (v - start) is a non-negative multiple of step and v is between start and stop inclusive. - step defaults to 1 for numeric inputs and INTERVAL 1 DAY for DATE or TIMESTAMP inputs. - A negative step generates a descending sequence; the step sign must match the direction from start to stop or an empty array is returned. - A zero step raises an error. - Very large ranges succeed syntactically but may exhaust memory because the array is materialised eagerly. ## Null and empty handling - NULL start, stop, or step returns NULL. - When start > stop with a positive step (or start < stop with a negative step), the result is an empty array. - start = stop returns a single-element array containing that value. - NULL elements are not produced; every position is a valid value. ## Compatibility - Matches the array/map SQL convention for SEQUENCE with inclusive endpoints. - For lazy or large-range iteration without materialising the array, prefer a relational row source (for example EXPLODE(SEQUENCE(...)) inside a CTE, or a generator function where available).
| Name | Type | Description |
|---|---|---|
start | Specifies the starting value of the sequence (inclusive). | |
stop | Specifies the ending value of the sequence (inclusive). Must be the same type family as start. | |
step | Specifies the increment between consecutive values. Defaults to 1 for integers and INTERVAL 1 DAY for dates and timestamps. Negative steps generate descending sequences. |
-- Integer sequence
SELECT SEQUENCE(1, 5); -- [1, 2, 3, 4, 5]-- Custom step
SELECT SEQUENCE(0, 10, 2); -- [0, 2, 4, 6, 8, 10]-- Descending sequence
SELECT SEQUENCE(5, 1, -1); -- [5, 4, 3, 2, 1]-- Date sequence for a calendar
SELECT SEQUENCE(DATE '2026-01-01', DATE '2026-01-05'); -- [2026-01-01..2026-01-05]-- Empty result when direction does not match start/stop
SELECT SEQUENCE(5, 1); -- []-- Build a per-session index vector for TRANSFORM
SELECT session_id, SEQUENCE(1, ARRAY_SIZE(events)) AS idx
FROM analytics.events.user_sessions;