sequence_next_node

Aggregate function that returns the value of the next event after a matched sequential pattern. Implements ClickHouse's sequenceNextNode for flow analysis.

Signature

sequence_next_node(direction VARCHAR, base VARCHAR, timestamp TIMESTAMP,
                   event_column VARCHAR, base_condition BOOLEAN,
                   event1 BOOLEAN [, event2 BOOLEAN, ...]) -> VARCHAR

Parameters:

Returns: VARCHAR (nullable) -- the value of the adjacent event after a successful sequential match, or NULL if no match or no adjacent event exists.

Direction

Controls which direction to scan for the next event:

Base

Selects the single anchor event the chain starts from:

If the chain does not match from that anchor, the result is NULL; other anchors are not tried.

Usage

-- What page do users visit after Home → Product?
SELECT user_id,
  sequence_next_node('forward', 'first_match', event_time, page,
    page = 'Home',        -- base_condition
    page = 'Home',        -- event1
    page = 'Product'      -- event2
  ) as next_page
FROM events
GROUP BY user_id;

-- What page did users come from before reaching Checkout?
SELECT user_id,
  sequence_next_node('backward', 'tail', event_time, page,
    page = 'Checkout',    -- base_condition
    page = 'Checkout'     -- event1
  ) as previous_page
FROM events
GROUP BY user_id;

-- Flow analysis: what happens after the first Home → Product → Cart sequence?
SELECT user_id,
  sequence_next_node('forward', 'first_match', event_time, page,
    page = 'Home',        -- base_condition
    page = 'Home',        -- event1
    page = 'Product',     -- event2
    page = 'Cart'         -- event3
  ) as next_after_cart
FROM events
GROUP BY user_id;

Behavior

Follows ClickHouse's sequenceNextNode (differential testing against ClickHouse 26.9.8.3 found no difference for events with distinct (timestamp, value)):

1. Events are sorted by (timestamp, value, base_condition, conditions). ClickHouse sorts by (timestamp, value) and keeps events tying on both in arrival order, so its result can change with row order; the extra keys make the order total here.
2. A single anchor is selected by base:
   • head / tail: the literal first/last event in sorted order, which must itself satisfy base_condition — otherwise the result is NULL.
   • first_match / last_match: the first/last event satisfying both base_condition and event1.
3. The chain must match consecutive events: eventK must hold at the K-th position from the anchor (ascending for forward, descending for backward). Interleaved non-matching events break the chain, and a failed chain is not retried at other anchors.
4. On a full match, the value of the event immediately after (forward) or before (backward) the chain is returned; NULL when that position falls off either end.
5. Returns NULL if no anchor exists or the chain does not match.

Differences from ClickHouse

Errors

Unknown configuration values abort the query with a descriptive SQL error instead of silently returning NULL:

• Unknown direction — expected 'forward' or 'backward'
• Unknown base — expected 'head', 'tail', 'first_match', or 'last_match'
• forward with tail, backward with head — the chain would start at an end of the sequence, so there is never an adjacent event (ClickHouse rejects these too)

A NULL direction is treated as 'forward' and a NULL base as 'first_match', so a NULL direction with base 'tail' is the forbidden forward/tail combination. Direction and base names are case-insensitive.

NULL inputs: a row with a NULL timestamp is skipped; a NULL value is kept as an event and can be returned as the next value; a NULL base_condition or event condition counts as false. (ClickHouse skips any row with a NULL argument.)

The direction and base arguments must be the same for every row of a group (normally a literal). A group with two different non-NULL values is an error, whatever the row order (... the direction argument must be the same for every row of a group). NULL values are ignored.

Timestamp types

TIMESTAMP and TIMESTAMPTZ are both accepted and read as microseconds since the epoch; for TIMESTAMPTZ that is the instant itself, independent of the session time zone. Casting TIMESTAMPTZ to TIMESTAMP instead converts to local time, which can reorder events around a daylight-saving change. TIMESTAMP_S, TIMESTAMP_MS and DATE are cast to TIMESTAMP implicitly. TIMESTAMP_NS is too, which truncates to microseconds: events less than a microsecond apart become ties.

Implementation

Note: Unlike other event-collecting functions where the Event struct is Copy (16 bytes), sequence_next_node uses a dedicated NextNodeEvent struct (32 bytes) that stores an Arc<str> value per event. The Arc<str> enables O(1) clone via reference counting, which significantly reduces combine overhead compared to per-event deep string copying.

See Also

• sequence_match -- check whether a pattern matches (boolean)
• sequence_count -- count non-overlapping matches of a pattern
• sequence_match_events -- return the timestamps of each matched step