Quick Reference

One-page cheat sheet for all duckdb-behavioral functions and patterns.

Installation

INSTALL behavioral FROM community;
LOAD behavioral;

Functions at a Glance

sessionize — Session IDs from inactivity gaps

sessionize(timestamp_col, INTERVAL 'gap') OVER (
  PARTITION BY user_id ORDER BY timestamp_col
) → BIGINT

Key facts: Aggregate designed to be used with OVER (... ORDER BY ...), where it returns 1-indexed session IDs per row. Without OVER it returns the number of sessions in the group. Never use OVER () or a BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame: those shapes crash DuckDB (duckdb/duckdb#26109).

retention — Cohort retention analysis

retention(cond1, cond2, ..., condN) → BOOLEAN[]

Key facts: Aggregate function. Returns array where result[i] is true if cond1 AND cond[i] were both satisfied somewhere in the group. Supports 2–32 conditions.

window_funnel — Conversion funnel steps

window_funnel(INTERVAL 'window', timestamp_col, cond1, cond2, ..., condN) → INTEGER
window_funnel(INTERVAL 'window', 'mode_str', timestamp_col, cond1, ...) → INTEGER

Key facts: Returns furthest step reached (0 = no step matched). Optional mode string before timestamp. Supports 1–32 conditions. One event that satisfies several consecutive conditions fills several steps (unless strict_once).

Modes:

Mode	Effect
`strict`	Matched condition must not refire before next step
`strict_deduplication`	Alias for `strict`
`strict_order`	No earlier conditions between matched steps
`strict_increase`	Strictly increasing timestamps between steps
`strict_once`	Each event advances at most one step
`allow_reentry`	Reset funnel when condition 1 fires again
`timestamp_dedup`	Skip events with same timestamp as previous step

Combine modes: 'strict_increase, strict_once'

window_funnel_events — When did each funnel step happen?

window_funnel_events(INTERVAL 'window', timestamp_col, cond1, ..., condN) → TIMESTAMP[]  -- TIMESTAMPTZ[] for TIMESTAMPTZ input
window_funnel_events(INTERVAL 'window', 'mode_str', timestamp_col, cond1, ...) → TIMESTAMP[]

Key facts: Returns one timestamp per matched step of the best funnel chain (list length equals window_funnel's result). Empty list when the entry condition never matched. Same modes and 1–32 condition limit as window_funnel.

sequence_match — Did pattern occur?

sequence_match('pattern', timestamp_col, cond1, cond2, ...) → BOOLEAN

sequence_match, sequence_count, and sequence_match_events take 2–32 conditions. One event cannot fill two pattern steps.

sequence_count — How many times?

sequence_count('pattern', timestamp_col, cond1, cond2, ...) → BIGINT

Counts non-overlapping matches.

sequence_match_events — When did each step match?

sequence_match_events('pattern', timestamp_col, cond1, cond2, ...) → LIST(TIMESTAMP)

Returns list of timestamps, one per matched condition in the pattern.

sequence_next_node — What happened next/before?

sequence_next_node('direction', 'base', timestamp_col, value_col,
  base_condition, event1_cond, event2_cond, ...) → VARCHAR

Directions: 'forward', 'backward'

Bases (each selects a single anchor):

Base	Anchor
`'head'`	The literal first event (sorted); it must satisfy `base_condition`, otherwise `NULL`
`'tail'`	The literal last event (sorted); it must satisfy `base_condition`, otherwise `NULL`
`'first_match'`	The first event satisfying `base_condition` AND `event1`
`'last_match'`	The last event satisfying `base_condition` AND `event1`

The chain event1, event2, ... must match consecutive events from the anchor (ascending for forward, descending for backward); a failed chain is not retried at another anchor. 'forward' with 'tail' and 'backward' with 'head' are rejected with an error, leaving 6 valid direction × base combinations. Takes a base condition plus 1–32 event conditions.

Pattern Syntax

Element	Syntax	Meaning
Condition match	`(?N)`	Event where condition N is true (1-indexed)
Any one event	`.`	Exactly one event, any conditions
Any events	`.*`	Zero or more events
Time ≤	`(?t<=N)`	At most N seconds since previous match
Time ≥	`(?t>=N)`	At least N seconds since previous match
Time <	`(?t<N)`	Less than N seconds
Time >	`(?t>N)`	More than N seconds
Time =	`(?t==N)`	Exactly N seconds
Time ≠	`(?t!=N)`	Not exactly N seconds

Common Patterns

Pattern	Description
`(?1).*(?2)`	Condition 1 then 2, any gap
`(?1)(?2)`	Condition 1 immediately followed by 2
`(?1).*(?t<=3600)(?2)`	Condition 1 then 2 within 1 hour
`(?1).(?2)`	Condition 1, exactly one event, then 2
`(?1).(?2).(?3)`	Three-step sequence, any gaps
`(?1).(?t<=300)(?2).(?t<=300)(?3)`	Three steps, each within 5 minutes

NULL Handling

Input	Behavior
NULL timestamp	Row is ignored
NULL boolean condition	Treated as `false`
NULL pattern string	`sequence_match`/`sequence_count` return NULL; `sequence_match_events` returns `[]`
Empty pattern string	Error (malformed pattern)
NULL `window_funnel` window	Row is ignored
NULL `window_funnel` mode	No mode (default behaviour)
NULL `sequence_next_node` direction / base	Treated as `'forward'` / `'first_match'`
NULL value in `sequence_next_node`	Stored and can be returned

behavioral_version — Which build is loaded?

behavioral_version() → VARCHAR   -- e.g. '0.10.1'

Key facts: Diagnostic scalar; useful when the community channel, a pinned install, and a local build could serve different versions.

Limits

Limit	Value
Boolean conditions	`window_funnel`, `window_funnel_events`: 1 – 32; `retention`, `sequence_match`, `sequence_count`, `sequence_match_events`: 2 – 32; `sequence_next_node`: base condition + 1 – 32
Interval type	No month-based intervals (days, hours, minutes, seconds only)
Timestamp type	`TIMESTAMP` or `TIMESTAMPTZ` (`*_events` then return `TIMESTAMPTZ[]`); `TIMESTAMP_NS` is truncated to microseconds
Pattern length	At most 1024 steps
Configuration arguments	Pattern, window, mode, direction and base must be the same for every row of a group
`sessionize`	Aggregate used with `OVER (... ORDER BY ...)` for per-row session IDs
All other aggregates	Normally used with `GROUP BY`

ClickHouse Translation

ClickHouse	DuckDB behavioral
`windowFunnel(3600)(ts, c1, c2)`	`window_funnel(INTERVAL '1 hour', ts, c1, c2)`
`sequenceMatch('pat')(ts, c1, c2)`	`sequence_match('pat', ts, c1, c2)`
`sequenceCount('pat')(ts, c1, c2)`	`sequence_count('pat', ts, c1, c2)`
`sequenceNextNode('forward', 'head')(ts, v, b, e1)`	`sequence_next_node('forward', 'head', ts, v, b, e1)`
`retention(c1, c2, c3)`	`retention(c1, c2, c3)`