Downloads 13.7kthis week

GitHub stars 16

Extension descriptor (YAML)

Behavioral analytics functions inspired by ClickHouse (sessionize, retention, window_funnel, window_funnel_events, sequence_match, sequence_count, sequence_match_events, sequence_next_node)

Compatibility Matrix

This table covers all DuckDB releases that have not reached end-of-life, including LTS releases, and shows the 2 most recent patch releases of each. Older patch releases are not listed, but the extension may be available for them as well.

Installing and Loading

INSTALL behavioral FROM community;
LOAD behavioral;

Example

-- Session assignment with 30-minute timeout
SELECT ts, sessionize(ts, INTERVAL '30 minutes') OVER (ORDER BY ts) AS session_id
FROM events;

-- Conversion funnel analysis
SELECT user_id, window_funnel(INTERVAL '1 hour', ts,
    event = 'view', event = 'cart', event = 'purchase')
FROM events GROUP BY user_id;

About behavioral

Behavioral analytics functions for DuckDB, following ClickHouse's parametric behavioral functions (differentially tested against ClickHouse 26.9):

• sessionize: Session IDs over an ordered window, based on timestamp gaps
• retention: Cohort retention analysis returning boolean arrays
• window_funnel: Conversion funnel step tracking with 6 composable modes
• window_funnel_events: Timestamps of the best funnel chain as LIST(TIMESTAMP)
• sequence_match: Pattern matching over event sequences
• sequence_count: Count non-overlapping pattern matches
• sequence_match_events: Return matched condition timestamps
• sequence_next_node: Find the next event value after a pattern match
• behavioral_version: Diagnostic scalar returning the loaded extension version

Up to 32 event conditions per call. Invalid configuration (unknown modes, malformed patterns, month-based intervals) raises descriptive SQL errors. Built on DuckDB's stable C API; pure Rust, with no unsafe code outside the FFI layer.

Known DuckDB limitation (duckdb/duckdb#26109, affects every C API aggregate): do not call these functions with ORDER BY inside the call, with OVER (), or over a frame written BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING; DuckDB can crash. None is needed: the functions sort by timestamp themselves, and OVER (PARTITION BY user_id) gives the whole-partition value.

Added Functions

Overloaded Functions

This extension does not add any function overloads.

Added Types

This extension does not add any types.

Added Settings