duckdb-behavioral

Behavioral analytics functions for DuckDB, inspired by ClickHouse.

Quick Start • Functions • Examples • Performance • Documentation

Provides sessionize, retention, window_funnel, window_funnel_events, sequence_match, sequence_count, sequence_match_events, and sequence_next_node as a loadable DuckDB extension written in Rust. Complete ClickHouse behavioral analytics parity.

Personal Project Disclaimer: This is a personal project developed on my own time. It is not affiliated with, endorsed by, or related to my employer or professional role in any way.

AI-Assisted Development: Built with Claude (Anthropic). Correctness is validated by automated testing — not assumed from AI output. See Quality.

• Quick Start
• Functions
• Examples
• Integrations
• Performance
• Community Extension
• Quality
• ClickHouse Parity Status
• Building
• Development
• Documentation
• Known Limitations
• Requirements
• License

-- Install from the DuckDB Community Extensions repository
INSTALL behavioral FROM community;
LOAD behavioral;

Or build from source (DuckDB loads only .duckdb_extension files that carry its metadata footer; make adds it):

git submodule update --init --recursive
make configure release
duckdb -unsigned -c "LOAD 'build/release/behavioral.duckdb_extension'; SELECT behavioral_version();"

Verify it works — run these after loading:

-- Session IDs (should return 1, 1, 2)
SELECT sessionize(ts, INTERVAL '30 minutes') OVER (ORDER BY ts) AS session_id
FROM (VALUES (TIMESTAMP '2024-01-01 10:00'), (TIMESTAMP '2024-01-01 10:10'),
             (TIMESTAMP '2024-01-01 12:00')) t(ts);

-- Retention (should return [true, false])
SELECT retention(true, false);

-- Funnel progress (should return 2: one event satisfying conditions 1 and 2
-- fills both steps, as in ClickHouse)
SELECT window_funnel(INTERVAL '1 hour', TIMESTAMP '2024-01-01', true, true, false);

Condition limits: window_funnel / window_funnel_events take 1 to 32 conditions, retention and the sequence_* pattern functions 2 to 32, and sequence_next_node a base condition plus 1 to 32 event conditions. behavioral_version() returns the loaded extension version for diagnostics. Invalid configuration (unknown modes, malformed patterns, out-of-range condition numbers, month-based intervals) raises descriptive SQL errors instead of silently wrong results. Results are deterministic under parallel execution: events sort with total tie-breaking keys, so thread count and row order never change a result (in two places ClickHouse's results do depend on row order; see ClickHouse Parity). Gaps touching DuckDB's ±infinity timestamps are computed exactly instead of wrapping. Detailed documentation, examples, and edge case behavior for each function: Function Reference

Track how far users progress through a purchase flow within 1 hour:

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;

Assign session IDs with a 30-minute inactivity gap, then compute metrics:

WITH sessionized AS (
    SELECT user_id, event_time,
      sessionize(event_time, INTERVAL '30 minutes') OVER (
        PARTITION BY user_id ORDER BY event_time
      ) as session_id
    FROM events
)
SELECT user_id, session_id,
  COUNT(*) as page_views,
  MIN(event_time) as session_start,
  MAX(event_time) as session_end
FROM sessionized
GROUP BY user_id, session_id;

Measure week-over-week retention for signup cohorts:

SELECT cohort_week,
  COUNT(*) as cohort_size,
  SUM(CASE WHEN r[1] THEN 1 ELSE 0 END) as week_0,
  SUM(CASE WHEN r[2] THEN 1 ELSE 0 END) as week_1,
  SUM(CASE WHEN r[3] THEN 1 ELSE 0 END) as week_2
FROM (
  SELECT user_id, cohort_week,
    retention(
      activity_date >= cohort_week AND activity_date < cohort_week + INTERVAL '7 days',
      activity_date >= cohort_week + INTERVAL '7 days' AND activity_date < cohort_week + INTERVAL '14 days',
      activity_date >= cohort_week + INTERVAL '14 days' AND activity_date < cohort_week + INTERVAL '21 days'
    ) as r
  FROM activity GROUP BY user_id, cohort_week
)
GROUP BY cohort_week ORDER BY cohort_week;

Find users who viewed then purchased within 1 hour:

SELECT user_id,
  sequence_match('(?1).*(?t<=3600)(?2)', event_time,
    event_type = 'page_view',
    event_type = 'purchase'
  ) as converted_within_hour
FROM events GROUP BY user_id;

Aggregate funnel results into a conversion report:

WITH funnels AS (
  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 step
  FROM events GROUP BY user_id
)
SELECT step as reached_step,
  COUNT(*) as users,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) as pct
FROM funnels GROUP BY step ORDER BY step;

Discover what page users visit after Home → Product. The inner query computes one next page per user; the outer query counts users per page (without the inner GROUP BY user_id, all users' events would form one sequence):

SELECT next_page, COUNT(*) AS user_count
FROM (
  SELECT user_id,
    sequence_next_node('forward', 'first_match', event_time, page,
      page = 'Home', page = 'Home', page = 'Product') AS next_page
  FROM events
  GROUP BY user_id
)
GROUP BY next_page
ORDER BY user_count DESC;

Count how many times users repeat a view → cart cycle:

SELECT user_id,
  sequence_count('(?1).*(?2)', event_time,
    event_type = 'page_view',
    event_type = 'add_to_cart'
  ) as view_cart_cycles
FROM events GROUP BY user_id ORDER BY view_cart_cycles DESC;

Get the exact timestamps when each funnel step was satisfied:

SELECT user_id,
  sequence_match_events('(?1).*(?2).*(?3)', event_time,
    event_type = 'page_view',
    event_type = 'add_to_cart',
    event_type = 'purchase'
  ) as step_timestamps
FROM events GROUP BY user_id;

For 5 complete real-world examples with sample data, see Use Cases. For a comprehensive recipe collection, see SQL Cookbook.

import duckdb

conn = duckdb.connect()
conn.execute("INSTALL behavioral FROM community")
conn.execute("LOAD behavioral")

df = conn.execute("""
    SELECT user_id,
      window_funnel(INTERVAL '1 hour', event_time,
        event_type = 'view', event_type = 'cart', event_type = 'purchase'
      ) as steps
    FROM events GROUP BY user_id
""").fetchdf()

const duckdb = require('duckdb');
const db = new duckdb.Database(':memory:');

db.run("INSTALL behavioral FROM community");
db.run("LOAD behavioral");

# profiles.yml
my_project:
  outputs:
    dev:
      type: duckdb
      extensions:
        - name: behavioral
          repo: community

-- Query any file format directly
SELECT user_id,
  window_funnel(INTERVAL '1 hour', event_time,
    event_type = 'view', event_type = 'purchase')
FROM read_parquet('events/*.parquet')
GROUP BY user_id;

All measurements below are Criterion.rs 0.8.2 microbenchmarks of the Rust state machines (not SQL queries) with 95% confidence intervals, recorded in PERF.md Session 15, before v0.8.0. They have not been re-measured since: v0.8.0 changed hot paths, and the window_funnel engine was replaced with ClickHouse's algorithm in this release (in an interleaved before/after run its finalize benchmark ranged from no measurable change to ~16% slower, see CHANGELOG.md).

Key design choices:

• 16-byte Copy events with u32 bitmask conditions — four events per cache line, zero heap allocation per event
• O(1) combine for sessionize and retention via boundary tracking and bitmask OR
• In-place combine for event-collecting functions — O(N) amortized instead of O(N^2) from repeated allocation
• Sequence fast paths — common pattern shapes dispatch to specialized O(n) linear scans; every other pattern uses a feasibility pass plus a greedy walk, O(s · n log n), replacing a backtracking search that was quadratic per group
• Presorted detection — O(n) check skips O(n log n) sort when events arrive in timestamp order

Optimization highlights:

Five attempted optimizations were measured, found to be regressions, and reverted. All negative results are documented in PERF.md.

Full methodology, per-session optimization history with confidence intervals, and reproducible benchmark instructions: PERF.md.

This extension is listed in the DuckDB Community Extensions repository ( Read the rest on GitHub