window_funnel

Aggregate function for conversion funnel analysis. Searches for the longest chain of sequential conditions within a time window. Returns the maximum funnel step reached.

Signature

window_funnel(window INTERVAL, timestamp TIMESTAMP,
              cond1 BOOLEAN, cond2 BOOLEAN [, ...]) -> INTEGER

window_funnel(window INTERVAL, mode VARCHAR, timestamp TIMESTAMP,
              cond1 BOOLEAN, cond2 BOOLEAN [, ...]) -> INTEGER

Parameters:

Returns: INTEGER -- the number of matched funnel steps (0 to N). A return value of 0 means the entry condition was never satisfied.

Usage

Default Mode

SELECT user_id,
  window_funnel(INTERVAL '1 hour', event_time,
    event_type = 'page_view',
    event_type = 'add_to_cart',
    event_type = 'checkout',
    event_type = 'purchase'
  ) as furthest_step
FROM events
GROUP BY user_id;

With Mode String

SELECT user_id,
  window_funnel(INTERVAL '1 hour', 'strict_increase, strict_once',
    event_time,
    event_type = 'page_view',
    event_type = 'add_to_cart',
    event_type = 'purchase'
  ) as furthest_step
FROM events
GROUP BY user_id;

Behavior

window_funnel follows ClickHouse's windowFunnel:

1. Each event contributes one entry per condition it satisfies, and entries are visited in (timestamp, condition) order.
2. An entry for cond1 starts a chain. An entry for condK extends a chain that has reached step K - 1, provided it is within window of that chain's entry event.
3. The result is the highest step any chain reached.

Because every satisfied condition is an entry, one event can fill several steps, including the entry step: an event satisfying cond1 and cond2 reaches step 2 on its own (strict_once and strict_increase prevent this). A gap exactly equal to window is inside the window.

Example

Given events for a user with a 1-hour window and 3-step funnel:

Result: 2

• Step 1 matched at 10:00 (page_view).
• Step 2 matched at 10:20 (add_to_cart, within 1 hour of 10:00).
• Step 3 at 11:30 is outside the 1-hour window from the entry at 10:00.

Determinism

Events sort by (timestamp, conditions) before the scan, so results are deterministic regardless of thread count, physical row order, or the order in which DuckDB's parallel aggregation combines partial states, including same-timestamp event bursts. (ClickHouse orders same-timestamp rows by arrival under strict_once; see ClickHouse Compatibility.)

Modes

Modes combine via a comma-separated string parameter, as in ClickHouse. Mode names are case-insensitive, and whitespace around them is ignored.

Under strict_increase this implementation keeps a valid chain that ClickHouse loses (events c1@0, c1@1, c2@1 give 2 here, 1 in ClickHouse); see ClickHouse Compatibility.

Mode Combinations

Modes can be combined freely:

-- Require strictly increasing timestamps and one step per event
window_funnel(INTERVAL '1 hour', 'strict_increase, strict_once',
  ts, cond1, cond2, cond3)

-- Strict order, skipping steps that arrive too early
window_funnel(INTERVAL '1 hour', 'strict_order, allow_reentry',
  ts, cond1, cond2, cond3)

Errors

Invalid configuration aborts the query with a descriptive SQL error instead of silently producing wrong results:

• Unknown mode string — window_funnel: unknown mode 'strict_typo'; valid modes are 'strict', 'strict_deduplication', 'strict_order', 'strict_increase', 'strict_once', 'allow_reentry', 'timestamp_dedup' (comma-separated for combinations)
• Month-based window — month intervals are ambiguous (28-31 days); use day/hour/minute/second units (e.g. INTERVAL '30 days')
• Negative window — the window must be non-negative
• allow_reentry without strict_order — window_funnel: mode 'allow_reentry' requires 'strict_order'

A row whose window is NULL is skipped, like a row with a NULL timestamp. A NULL mode means no mode.

The window and mode 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 window 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

Events are collected during the update phase and sorted during finalize. One pass over the sorted events keeps, per funnel step, the chain with the latest entry (and, for strict_increase, separately the best chain ending before the current timestamp). Under strict_once, whether a chain can use distinct same-timestamp events for consecutive steps is decided by bipartite matching (at most 32 steps), so a large same-timestamp burst stays polynomial.

The last recorded benchmark (PERF.md Session 15, the previous engine) is 100 million events in 791 ms; it has not been re-measured for this engine.

See Also

• sequence_match -- pattern matching for more flexible event sequences
• sequence_count -- count non-overlapping pattern occurrences
• sequence_next_node -- find what happens after a matched pattern
• ClickHouse Compatibility -- full compatibility matrix including all mode mappings