duckdb-behavioral

Behavioral analytics for DuckDB -- session analysis, conversion funnels, retention cohorts, and event sequence pattern matching, all inside your SQL queries.

duckdb-behavioral is a loadable DuckDB extension written in Rust that brings ClickHouse-style behavioral analytics functions to DuckDB. It ships eight battle-tested functions that cover the core patterns of user behavior analysis — all six ClickHouse behavioral functions with source-verified semantics, plus two extensions — and benchmark-validated performance at billion-row scale.

No external services, no data pipelines, no additional infrastructure. Load the extension, write SQL, get answers.

What Can You Do With This?

Session Analysis

Break a continuous stream of events into logical sessions based on inactivity gaps. Identify how many sessions a user has per day, how long each session lasts, and where sessions begin and end.

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;

Conversion Funnels

Track how far users progress through a multi-step conversion funnel (page view, add to cart, checkout, purchase) within a time window. Identify exactly where users drop off.

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;

Retention Cohorts

Measure whether users who appeared in a cohort (e.g., signed up in January) returned in subsequent periods. Build the classic retention triangle directly in SQL.

SELECT cohort_month,
  retention(
    activity_date = cohort_month,
    activity_date = cohort_month + INTERVAL '1 month',
    activity_date = cohort_month + INTERVAL '2 months',
    activity_date = cohort_month + INTERVAL '3 months'
  ) as retained
FROM user_activity
GROUP BY user_id, cohort_month;

Event Sequence Pattern Matching

Detect complex behavioral patterns using a mini-regex over event conditions. Find users who viewed a product, then purchased within one hour -- with any number of intervening events.

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

User Journey / Flow Analysis

Discover what users do after a specific behavioral sequence. What page do users visit after navigating from Home to 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;

Quick Installation

Community Extension

The extension is listed in the DuckDB Community Extensions repository:

INSTALL behavioral FROM community;
LOAD behavioral;

No build tools, compilation, or -unsigned flag required.

From Source

git clone --recurse-submodules https://github.com/tomtom215/duckdb-behavioral.git
cd duckdb-behavioral
make configure release   # cargo build --release + metadata footer

This produces build/release/behavioral.duckdb_extension. DuckDB only loads files ending in .duckdb_extension, so the raw target/release/libbehavioral.so (or .dylib) cannot be loaded directly. Locally built extensions are unsigned, so start DuckDB with -unsigned:

duckdb -unsigned -c "LOAD 'build/release/behavioral.duckdb_extension'; SELECT behavioral_version();"

For detailed installation instructions, troubleshooting, and a complete worked example, see the Getting Started guide.

Functions

Choosing the Right Function

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#ffffff', 'primaryTextColor': '#1a1a1a', 'primaryBorderColor': '#333333', 'lineColor': '#333333', 'secondaryColor': '#f5f5f5', 'tertiaryColor': '#e0e0e0', 'textColor': '#1a1a1a'}}}%%
flowchart TD
    Q{What do you want<br/>to analyze?}
    Q -->|"Break events<br/>into sessions"| S["sessionize"]
    Q -->|"Did users come<br/>back over time?"| R["retention"]
    Q -->|"How far through<br/>a multi-step flow?"| WF["window_funnel"]
    WF -->|"When did each<br/>step happen?"| WFE["window_funnel_events"]
    Q -->|"Did a specific<br/>event pattern occur?"| SM{Need details?}
    SM -->|"Yes/No answer"| SEQ["sequence_match"]
    SM -->|"How many times?"| SC["sequence_count"]
    SM -->|"When did each<br/>step happen?"| SME["sequence_match_events"]
    Q -->|"What happened<br/>next/before?"| SNN["sequence_next_node"]

    style Q fill:#ffffff,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style S fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style R fill:#d9d9d9,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style WF fill:#f0f0f0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style WFE fill:#f0f0f0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SM fill:#ffffff,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SEQ fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SC fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SME fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SNN fill:#d9d9d9,stroke:#333333,stroke-width:2px,color:#1a1a1a

Eight aggregate functions plus one diagnostic scalar:

Condition limits: window_funnel and window_funnel_events accept 1 to 32 conditions; retention, sequence_match, sequence_count, and sequence_match_events accept 2 to 32; sequence_next_node accepts a base condition plus 1 to 32 event conditions (ClickHouse allows up to 64 for sequenceNextNode). See the ClickHouse Compatibility page for the full parity matrix.

Performance

The numbers below are Criterion.rs microbenchmarks of the Rust aggregate state (update/combine/finalize), not end-to-end SQL queries, with 95% confidence intervals. They are the PERF.md Session 15 headline numbers, recorded before v0.8.0 and not re-measured since.

Key design choices that enable this performance:

• 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 already arrive in timestamp order

Full methodology, per-element cost analysis, and optimization history are documented in the Performance section.

Engineering Highlights

This project demonstrates depth across systems programming, database internals, algorithm design, performance engineering, and software quality practices. For a comprehensive technical overview, see the Engineering Overview.

Documentation

Requirements

• DuckDB 1.3.2 or later (built against 2.0.0; stable C API, so one binary serves every release CI checks: 1.3.2, 1.4.4, 1.5.0, 1.5.6, 2.0.0)
• Rust 1.88+ (MSRV) for building from source
• Python 3 and make for make configure release (stamps the metadata footer)

Source Code

The source code is available at github.com/tomtom215/duckdb-behavioral.

License

MIT

Getting Started

This guide walks you through installing the extension, loading it into DuckDB, verifying it works, and running your first behavioral analysis from start to finish.

Installation

Option 1: Community Extension (Recommended)

The extension is listed in the DuckDB Community Extensions repository. Install with a single command:

INSTALL behavioral FROM community;
LOAD behavioral;

No build tools, compilation, or -unsigned flag required. This works with any DuckDB client (CLI, Python, Node.js, Java, etc.).

Option 2: Build from Source

Building from source gives you the latest development version and works on any platform where Rust and DuckDB are available.

Prerequisites:

• Rust 1.88 or later (rustup recommended)
• A C toolchain/linker (gcc or clang) for linking the cdylib
• Python 3 and make (for make configure release, which stamps the metadata footer)
• DuckDB CLI v1.3.2 or later (for running queries; CI checks v1.3.2, v1.4.4, v1.5.0, v1.5.6 and v2.0.0)

Build steps:

# Clone the repository (with the extension-ci-tools submodule)
git clone --recurse-submodules https://github.com/tomtom215/duckdb-behavioral.git
cd duckdb-behavioral

# Build in release mode and append the DuckDB metadata footer
make configure release

This produces build/release/behavioral.duckdb_extension, ready to LOAD.

cargo build --release alone produces target/release/libbehavioral.so (.dylib on macOS), which DuckDB refuses to load: it only accepts files ending in .duckdb_extension that carry the metadata footer. To stamp the footer by hand instead of using make:

# Initialize the submodule (first time only)
git submodule update --init --recursive

# Copy the built library
cp target/release/libbehavioral.so /tmp/behavioral.duckdb_extension

# Append extension metadata
python3 extension-ci-tools/scripts/append_extension_metadata.py \
  -l /tmp/behavioral.duckdb_extension -n behavioral \
  -p linux_amd64 -dv v1.2.0 -ev v0.10.1 \
  -o /tmp/behavioral.duckdb_extension

Platform note: Replace linux_amd64 with your platform identifier (linux_arm64, osx_amd64, osx_arm64) and .so with .dylib on macOS.

Version note: -dv v1.2.0 is the DuckDB C API version, not a DuckDB release. The extension uses only the stable C API, so the default ABI type (C_STRUCT) applies and one build loads into any DuckDB release whose C API is v1.2.0 or newer. Do not pass --abi-type C_STRUCT_UNSTABLE: that pins the binary to the single release named by -dv.

Loading the Extension

From the Community Extension (Recommended)

-- No special flags needed
INSTALL behavioral FROM community;
LOAD behavioral;

This works in any DuckDB client:

import duckdb

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

From a Local Build

# The -unsigned flag is required for locally-built extensions
duckdb -unsigned

Then inside the DuckDB prompt:

LOAD 'build/release/behavioral.duckdb_extension';  -- from `make configure release`
-- or, if you stamped the footer by hand:
LOAD '/tmp/behavioral.duckdb_extension';

One-liner

duckdb -unsigned -c "LOAD 'build/release/behavioral.duckdb_extension'; SELECT behavioral_version();"

From a DuckDB Client Library

import duckdb

conn = duckdb.connect(config={"allow_unsigned_extensions": "true"})
conn.execute("LOAD '/tmp/behavioral.duckdb_extension'")

Once loaded, all eight aggregate functions are available in the current session: sessionize, retention, window_funnel, window_funnel_events, sequence_match, sequence_count, sequence_match_events, and sequence_next_node, plus the behavioral_version() scalar.

Verifying the Installation

Run these minimal queries to confirm each function category is working:

-- Sessionize: should return session IDs 1, 1, 2 (the 50-minute gap starts a new session)
SELECT ts, sessionize(ts, INTERVAL '30 minutes') OVER (ORDER BY ts) AS session_id
FROM (VALUES (TIMESTAMP '2024-01-01 10:00:00'),
             (TIMESTAMP '2024-01-01 10:10:00'),
             (TIMESTAMP '2024-01-01 11:00:00')) AS t(ts);

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

-- Window funnel: should return 1
SELECT window_funnel(INTERVAL '1 hour', TIMESTAMP '2024-01-01', true, false);

-- Sequence match / count over two events: should return true and 1
SELECT sequence_match('(?1).*(?2)', ts, a, b) AS matched,
       sequence_count('(?1).*(?2)', ts, a, b) AS cnt
FROM (VALUES (TIMESTAMP '2024-01-01 10:00:00', true, false),
             (TIMESTAMP '2024-01-01 10:05:00', false, true)) AS t(ts, a, b);

A single event cannot fill two pattern steps, so sequence_match('(?1).*(?2)', TIMESTAMP '2024-01-01', true, true) returns false (and sequence_count returns 0).

You can also verify all functions registered correctly by querying DuckDB's function catalog:

SELECT function_name FROM duckdb_functions()
WHERE function_name IN (
  'sessionize', 'retention', 'window_funnel', 'window_funnel_events',
  'sequence_match', 'sequence_count',
  'sequence_match_events', 'sequence_next_node', 'behavioral_version'
)
GROUP BY function_name
ORDER BY function_name;

This should return all nine function names (eight analytics functions plus the behavioral_version() diagnostic scalar). SELECT behavioral_version(); tells you which build is loaded.

Your First Analysis

This walkthrough creates sample e-commerce event data and demonstrates four core use cases: sessions, funnels, retention, and pattern matching.

Step 1: Create Sample Data

-- Create an events table with typical e-commerce data
CREATE TABLE events AS SELECT * FROM (VALUES
  (1, TIMESTAMP '2024-01-15 09:00:00', 'page_view',    'Home'),
  (1, TIMESTAMP '2024-01-15 09:05:00', 'page_view',    'Product'),
  (1, TIMESTAMP '2024-01-15 09:08:00', 'add_to_cart',  'Product'),
  (1, TIMESTAMP '2024-01-15 09:12:00', 'checkout',     'Cart'),
  (1, TIMESTAMP '2024-01-15 09:15:00', 'purchase',     'Checkout'),
  (2, TIMESTAMP '2024-01-15 10:00:00', 'page_view',    'Home'),
  (2, TIMESTAMP '2024-01-15 10:10:00', 'page_view',    'Product'),
  (2, TIMESTAMP '2024-01-15 10:20:00', 'add_to_cart',  'Product'),
  (2, TIMESTAMP '2024-01-15 14:00:00', 'page_view',    'Home'),
  (2, TIMESTAMP '2024-01-15 14:05:00', 'page_view',    'Product'),
  (3, TIMESTAMP '2024-01-15 11:00:00', 'page_view',    'Home'),
  (3, TIMESTAMP '2024-01-15 11:30:00', 'page_view',    'Blog'),
  (3, TIMESTAMP '2024-01-15 12:00:00', 'page_view',    'Home'),
  (3, TIMESTAMP '2024-01-16 09:00:00', 'page_view',    'Home'),
  (3, TIMESTAMP '2024-01-16 09:10:00', 'page_view',    'Product'),
  (3, TIMESTAMP '2024-01-16 09:15:00', 'add_to_cart',  'Product'),
  (3, TIMESTAMP '2024-01-16 09:20:00', 'checkout',     'Cart'),
  (3, TIMESTAMP '2024-01-16 09:25:00', 'purchase',     'Checkout')
) AS t(user_id, event_time, event_type, page);

Step 2: Identify User Sessions

Break events into sessions using a 30-minute inactivity threshold:

SELECT user_id, event_time, event_type,
  sessionize(event_time, INTERVAL '30 minutes') OVER (
    PARTITION BY user_id ORDER BY event_time
  ) as session_id
FROM events
ORDER BY user_id, event_time;

What to expect: User 1 has a single session (all events within 30 minutes). User 2 has two sessions (the 3h 40m gap between 10:20 and 14:00 starts a new session). User 3 has two sessions: the three events on Jan 15 are 30 minutes apart, which does not exceed the threshold, and the overnight gap before Jan 16 09:00 starts session 2.

Step 3: Analyze the Conversion Funnel

Track how far each user progresses through the purchase funnel within a 1-hour window:

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
ORDER BY user_id;

What to expect:

Step 4: Detect Purchase Patterns

Find which users viewed a product and then purchased (with any events in between):

SELECT user_id,
  sequence_match('(?1).*(?2)', event_time,
    event_type = 'page_view',
    event_type = 'purchase'
  ) as viewed_then_purchased
FROM events
GROUP BY user_id
ORDER BY user_id;

What to expect: Users 1 and 3 return true (they both viewed and purchased). User 2 returns false (viewed but never purchased).

Step 5: User Journey Flow

Discover where users navigate after viewing the Home page then the Product page:

SELECT user_id,
  sequence_next_node('forward', 'first_match', event_time, page,
    page = 'Home',
    page = 'Home',
    page = 'Product'
  ) as next_page_after_product
FROM events
GROUP BY user_id
ORDER BY user_id;

What to expect:

The function returns the page value of the event immediately following the matched Home -> Product sequence. For users 1 and 2 that is the add_to_cart event, which happened on the Product page. With 'first_match', the anchor is the first event satisfying the base condition and event1 (the first Home view); the chain must match consecutive events. User 3's first Home view is followed by Blog, so the chain fails, and it is not retried at the later Home view: the result is NULL.

Troubleshooting

Extension fails to load

"file was built for DuckDB C API version '...' but we can only load extensions built for DuckDB C API '...'"

The extension declares the minimum DuckDB C API version it needs (v1.2.0); your DuckDB is older than that. Check your version with:

duckdb --version

"The file was built specifically for DuckDB version '...'"

The binary was stamped C_STRUCT_UNSTABLE, which pins it to one DuckDB release. Re-append the metadata as shown above, without --abi-type.

"... could not be loaded because its signature is either missing or invalid and unsigned extensions are disabled by configuration."

This only applies to locally-built extensions. If you installed via INSTALL behavioral FROM community, the extension is already signed and this error should not occur.

For locally-built extensions, DuckDB rejects unsigned extensions by default. Use one of these approaches:

# CLI flag
duckdb -unsigned

# Or set inside a session (before LOAD)
SET allow_unsigned_extensions = true;

# Python client
conn = duckdb.connect(config={"allow_unsigned_extensions": "true"})

"IO Error: Extension ... not found."

The path to the extension must be an absolute path or a path relative to the DuckDB working directory. Verify the file exists:

ls -la build/release/behavioral.duckdb_extension

"DuckDB extensions are files ending with '.duckdb_extension', loading different files is not possible"

You pointed LOAD at the raw libbehavioral.so/.dylib. Load the stamped build/release/behavioral.duckdb_extension from make configure release, or copy the library and run append_extension_metadata.py as shown above.

Platform mismatch

An extension built on Linux cannot be loaded on macOS, and vice versa. The extension must be built on the same platform and architecture where DuckDB is running.

Functions not found after loading

If LOAD succeeds but functions are not available, verify registration by querying the function catalog:

SELECT function_name, function_type
FROM duckdb_functions()
WHERE function_name LIKE 'session%'
   OR function_name LIKE 'retention%'
   OR function_name LIKE 'window_funnel%'
   OR function_name LIKE 'sequence%';

All eight aggregate functions should appear. If some are missing, check that you loaded the current build (SELECT behavioral_version();) and that your DuckDB is v1.3.2 or newer.

Query errors

"Binder Error: No function matches the given name and argument types"

This usually means the argument types do not match any registered overload. Common causes:

• Wrong argument order: Each function has a specific parameter order. See the Function Reference for exact signatures.
• Using INTEGER instead of INTERVAL: The window/gap parameter for sessionize and window_funnel must be a DuckDB INTERVAL, not an integer. Use INTERVAL '1 hour', not 3600.
• Too few boolean conditions: retention, sequence_match, sequence_count, and sequence_match_events require at least 2; window_funnel and window_funnel_events at least 1; sequence_next_node a base condition plus at least 1 event condition.
• More than 32 conditions: The maximum is 32 (for sequence_next_node, 32 event conditions after the base condition).

"NULL results when expecting values"

• Rows with NULL timestamps are silently ignored during aggregation.
• NULL boolean conditions are treated as false.
• sequence_next_node returns NULL when no pattern match is found or when no adjacent event exists after the match.

Build errors

"error: linker 'cc' not found"

Install a C compiler. On Ubuntu/Debian: sudo apt install build-essential. On macOS: xcode-select --install.

Test build fails to link against libduckdb

The duckdb dev-dependency is built without the bundled feature, so a bare cargo test has no DuckDB library to link. Set DUCKDB_DOWNLOAD_LIB=1 to have libduckdb-sys download a prebuilt libduckdb (cached in target/duckdb-download/), or point DUCKDB_LIB_DIR (plus LD_LIBRARY_PATH on Linux) at an existing one. Neither is needed for cargo build --release.

Running Tests

The extension includes 547 unit tests, 28 in-process integration tests (tests/extension_load.rs), and 1 doc-test:

DUCKDB_DOWNLOAD_LIB=1 cargo test

The integration tests build the release cdylib, stamp it, and LOAD it into an in-memory DuckDB, so a cold run takes about 20 seconds. Zero clippy warnings are enforced:

cargo clippy --all-targets

Running Benchmarks

Criterion.rs benchmarks cover the Rust state of every aggregate function, at scales from 100 elements up to 1 billion (sessionize_update), 100 million (most others), or 10 million (sequence_next_node):

# Run all benchmarks
cargo bench

# Run a specific benchmark group
cargo bench -- sessionize
cargo bench -- window_funnel
cargo bench -- sequence_match

Results are stored in target/criterion/ and automatically compared against previous runs by Criterion.

Project Structure

src/
  lib.rs                  # Entry point via quack_rs::entry_point_v2! macro
  common/
    mod.rs
    event.rs              # Shared Event type (16-byte bitmask)
    timestamp.rs          # Interval-to-microseconds conversion
  pattern/
    mod.rs
    parser.rs             # Recursive descent pattern parser
    executor.rs           # Pattern matcher: fast paths + feasibility/greedy matcher
    reference_nfa.rs      # Test-only: original backtracking search (oracle)
  sessionize.rs           # Session boundary tracking
  retention.rs            # Bitmask-based cohort retention
  window_funnel.rs        # Port of ClickHouse windowFunnel, mode flags
  sequence.rs             # Pattern matching state management
  sequence_next_node.rs   # Next event value after pattern match
  ffi/
    mod.rs                # register_all() dispatcher
    sessionize.rs         # Sessionize FFI callbacks
    retention.rs          # Retention FFI callbacks
    window_funnel.rs      # Window funnel FFI callbacks
    window_funnel_events.rs   # Window funnel events FFI callbacks
    sequence.rs           # Sequence match/count FFI callbacks
    sequence_match_events.rs  # Sequence match events FFI callbacks
    sequence_next_node.rs     # Sequence next node FFI callbacks
    version.rs            # behavioral_version() scalar

For a detailed discussion of the architecture, see Architecture.

Next Steps

• Function Reference -- detailed documentation for each function, including all parameters, modes, and edge case behavior
• FAQ -- answers to common questions about patterns, modes, NULL handling, and performance
• ClickHouse Compatibility -- how each function maps to its ClickHouse equivalent, with syntax translation examples
• Contributing -- development setup, testing expectations, and the PR process for contributing changes

Use Cases

This page presents five real-world use cases for duckdb-behavioral, each with a problem description, sample data, the analytical query, and interpretation of results. All examples are self-contained -- you can copy and paste each section into a DuckDB session with the extension loaded.

E-Commerce Conversion Funnel Analysis

Problem

An e-commerce team wants to measure how far users progress through the purchase funnel: product page view, add to cart, begin checkout, and complete purchase. Understanding where users drop off helps prioritize UX improvements. The team needs per-user funnel progress within a 1-hour conversion window, and an aggregate drop-off report across all users.

Sample Data

CREATE TABLE ecommerce_events (
    user_id    VARCHAR NOT NULL,
    event_time TIMESTAMP NOT NULL,
    event_type VARCHAR NOT NULL,
    product_id VARCHAR,
    revenue    DECIMAL(10,2)
);

INSERT INTO ecommerce_events VALUES
    -- User A: completes full funnel
    ('user_a', '2024-01-15 10:00:00', 'page_view',    'prod_1', NULL),
    ('user_a', '2024-01-15 10:05:00', 'add_to_cart',  'prod_1', NULL),
    ('user_a', '2024-01-15 10:12:00', 'checkout',     'prod_1', NULL),
    ('user_a', '2024-01-15 10:15:00', 'purchase',     'prod_1', 49.99),

    -- User B: views and adds to cart, but abandons
    ('user_b', '2024-01-15 11:00:00', 'page_view',    'prod_2', NULL),
    ('user_b', '2024-01-15 11:03:00', 'add_to_cart',  'prod_2', NULL),
    ('user_b', '2024-01-15 11:30:00', 'page_view',    'prod_3', NULL),

    -- User C: views only, never adds to cart
    ('user_c', '2024-01-15 14:00:00', 'page_view',    'prod_1', NULL),
    ('user_c', '2024-01-15 14:10:00', 'page_view',    'prod_4', NULL),

    -- User D: completes funnel but checkout is outside window
    ('user_d', '2024-01-15 09:00:00', 'page_view',    'prod_5', NULL),
    ('user_d', '2024-01-15 09:10:00', 'add_to_cart',  'prod_5', NULL),
    ('user_d', '2024-01-15 10:30:00', 'checkout',     'prod_5', NULL),
    ('user_d', '2024-01-15 10:35:00', 'purchase',     'prod_5', 29.99);

Analytical Query

-- Step 1: Per-user funnel progress
WITH user_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 furthest_step
    FROM ecommerce_events
    GROUP BY user_id
)
-- Step 2: Aggregate into a drop-off report
SELECT
    furthest_step,
    COUNT(*) AS user_count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM user_funnels
GROUP BY furthest_step
ORDER BY furthest_step;

Expected Results

Interpretation

• User A reached step 4 (purchase) -- the full funnel was completed within 1 hour.
• User B reached step 2 (add to cart). The second page view did not advance the funnel further because it matched step 1 again, not step 3.
• User C reached step 1 (page view only). No add-to-cart event occurred.
• User D reached step 2 (add to cart). Although checkout and purchase events exist, they occurred more than 1 hour after the initial page view (09:00 to 10:30 is 90 minutes), so they fall outside the window.

The drop-off report shows 50% of users stalling at the add-to-cart stage, which suggests the checkout flow needs attention. To use strict_increase mode and ensure each step has a strictly later timestamp:

window_funnel(
    INTERVAL '1 hour',
    'strict_increase',
    event_time,
    event_type = 'page_view',
    event_type = 'add_to_cart',
    event_type = 'checkout',
    event_type = 'purchase'
)

SaaS User Retention Cohort Analysis

Problem

A SaaS product team wants to measure weekly retention: of users who were active in week 0, what fraction returned in week 1, week 2, and week 3? This cohort analysis helps track product stickiness and identify retention trends across signup cohorts.

Sample Data

CREATE TABLE saas_activity (
    user_id       VARCHAR NOT NULL,
    activity_date DATE NOT NULL,
    cohort_week   DATE NOT NULL  -- the Monday of the user's signup week
);

INSERT INTO saas_activity VALUES
    -- User A: active in weeks 0, 1, and 3
    ('user_a', '2024-01-01', '2024-01-01'),
    ('user_a', '2024-01-09', '2024-01-01'),
    ('user_a', '2024-01-22', '2024-01-01'),

    -- User B: active in weeks 0 and 1 only
    ('user_b', '2024-01-02', '2024-01-01'),
    ('user_b', '2024-01-08', '2024-01-01'),

    -- User C: active in week 0 only
    ('user_c', '2024-01-03', '2024-01-01'),

    -- User D: active in weeks 0, 1, 2, and 3
    ('user_d', '2024-01-01', '2024-01-01'),
    ('user_d', '2024-01-10', '2024-01-01'),
    ('user_d', '2024-01-15', '2024-01-01'),
    ('user_d', '2024-01-23', '2024-01-01'),

    -- User E: different cohort (week of Jan 8), active in weeks 0 and 2
    ('user_e', '2024-01-08', '2024-01-08'),
    ('user_e', '2024-01-22', '2024-01-08');

Analytical Query

-- Per-user retention array
WITH user_retention AS (
    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',
            activity_date >= cohort_week + INTERVAL '21 days'
                AND activity_date < cohort_week + INTERVAL '28 days'
        ) AS retained
    FROM saas_activity
    GROUP BY user_id, cohort_week
)
-- Aggregate retention rates per cohort
SELECT
    cohort_week,
    COUNT(*) AS cohort_size,
    SUM(CASE WHEN retained[1] THEN 1 ELSE 0 END) AS week_0,
    SUM(CASE WHEN retained[2] THEN 1 ELSE 0 END) AS week_1,
    SUM(CASE WHEN retained[3] THEN 1 ELSE 0 END) AS week_2,
    SUM(CASE WHEN retained[4] THEN 1 ELSE 0 END) AS week_3,
    ROUND(100.0 * SUM(CASE WHEN retained[2] THEN 1 ELSE 0 END) /
        NULLIF(SUM(CASE WHEN retained[1] THEN 1 ELSE 0 END), 0), 1)
        AS week_1_pct,
    ROUND(100.0 * SUM(CASE WHEN retained[3] THEN 1 ELSE 0 END) /
        NULLIF(SUM(CASE WHEN retained[1] THEN 1 ELSE 0 END), 0), 1)
        AS week_2_pct,
    ROUND(100.0 * SUM(CASE WHEN retained[4] THEN 1 ELSE 0 END) /
        NULLIF(SUM(CASE WHEN retained[1] THEN 1 ELSE 0 END), 0), 1)
        AS week_3_pct
FROM user_retention
GROUP BY cohort_week
ORDER BY cohort_week;

Expected Results

Interpretation

For the January 1 cohort (4 users):

• All 4 users were active in week 0 (by definition, since they have activity in the cohort week).
• 3 of 4 (75%) returned in week 1 (users A, B, D).
• Only 1 of 4 (25%) was active in week 2 (user D).
• 2 of 4 (50%) returned in week 3 (users A, D).

The retention curve shows a steep drop after week 1, with partial recovery in week 3. This "smile curve" pattern suggests users who survive week 1 become long-term engaged, while week 2 is a critical churn risk period.

Note that retention checks whether the anchor condition (week 0) and each subsequent condition were both satisfied somewhere in the group. The conditions do not need to be satisfied by the same row.

Web Analytics Session Analysis

Problem

A web analytics team needs to segment user activity into sessions based on a 30-minute inactivity threshold, then compute session-level metrics: page count per session, session duration, and sessions per user. This is foundational for engagement analysis, bounce rate computation, and session-level attribution.

Sample Data

CREATE TABLE page_views (
    user_id    VARCHAR NOT NULL,
    event_time TIMESTAMP NOT NULL,
    page_url   VARCHAR NOT NULL,
    referrer   VARCHAR
);

INSERT INTO page_views VALUES
    -- User A: two distinct sessions
    ('user_a', '2024-01-15 10:00:00', '/home',     'google.com'),
    ('user_a', '2024-01-15 10:05:00', '/products',  NULL),
    ('user_a', '2024-01-15 10:08:00', '/product/1', NULL),
    ('user_a', '2024-01-15 10:25:00', '/cart',       NULL),
    -- 2-hour gap
    ('user_a', '2024-01-15 12:30:00', '/home',     'email-campaign'),
    ('user_a', '2024-01-15 12:35:00', '/products',  NULL),

    -- User B: single long session
    ('user_b', '2024-01-15 09:00:00', '/home',     'direct'),
    ('user_b', '2024-01-15 09:10:00', '/about',     NULL),
    ('user_b', '2024-01-15 09:15:00', '/pricing',   NULL),
    ('user_b', '2024-01-15 09:20:00', '/signup',    NULL),

    -- User C: three short sessions (bounce-like)
    ('user_c', '2024-01-15 08:00:00', '/home',     'google.com'),
    -- 45-minute gap
    ('user_c', '2024-01-15 08:45:00', '/home',     'google.com'),
    -- 1-hour gap
    ('user_c', '2024-01-15 09:45:00', '/blog/1',   'twitter.com');

Analytical Query

-- Step 1: Assign session IDs
WITH sessionized AS (
    SELECT
        user_id,
        event_time,
        page_url,
        referrer,
        sessionize(event_time, INTERVAL '30 minutes') OVER (
            PARTITION BY user_id ORDER BY event_time
        ) AS session_id
    FROM page_views
),
-- Step 2: Session-level metrics
session_metrics AS (
    SELECT
        user_id,
        session_id,
        COUNT(*) AS pages_viewed,
        MIN(event_time) AS session_start,
        MAX(event_time) AS session_end,
        EXTRACT(EPOCH FROM MAX(event_time) - MIN(event_time)) AS duration_seconds,
        FIRST(referrer) AS entry_referrer
    FROM sessionized
    GROUP BY user_id, session_id
)
-- Step 3: User-level summary
SELECT
    user_id,
    COUNT(*) AS total_sessions,
    ROUND(AVG(pages_viewed), 1) AS avg_pages_per_session,
    ROUND(AVG(duration_seconds), 0) AS avg_duration_seconds,
    SUM(CASE WHEN pages_viewed = 1 THEN 1 ELSE 0 END) AS bounce_sessions
FROM session_metrics
GROUP BY user_id
ORDER BY user_id;

Expected Results

Interpretation

• User A had 2 sessions. The first session (4 pages, 25 minutes from 10:00 to 10:25) shows engaged browsing. The 2-hour gap started a new session (2 pages, 5 minutes) from an email campaign.
• User B had 1 session spanning 20 minutes across 4 pages, ending at the signup page -- a high-intent user.
• User C had 3 bounce sessions (1 page each). Each visit had a gap exceeding 30 minutes, so each was a separate session. All sessions have zero duration because there is only one page view per session.

This analysis shows that User C is a repeat but non-engaged visitor, while Users A and B demonstrate meaningful engagement. The entry_referrer field from the session CTE can be used for channel attribution at the session level.

User Journey and Flow Analysis

Problem

A product analytics team wants to understand user navigation flow: after visiting the Home page and then the Product page, what page do users visit next? This "next node" analysis reveals common user journeys and helps identify navigation bottlenecks. The team also wants to understand what pages lead users to the Checkout page (backward analysis).

Sample Data

CREATE TABLE navigation_events (
    user_id    VARCHAR NOT NULL,
    event_time TIMESTAMP NOT NULL,
    page       VARCHAR NOT NULL
);

INSERT INTO navigation_events VALUES
    -- User A: Home -> Product -> Cart -> Checkout -> Confirmation
    ('user_a', '2024-01-15 10:00:00', 'Home'),
    ('user_a', '2024-01-15 10:02:00', 'Product'),
    ('user_a', '2024-01-15 10:05:00', 'Cart'),
    ('user_a', '2024-01-15 10:08:00', 'Checkout'),
    ('user_a', '2024-01-15 10:10:00', 'Confirmation'),

    -- User B: Home -> Product -> Product -> Home (browsing, no conversion)
    ('user_b', '2024-01-15 11:00:00', 'Home'),
    ('user_b', '2024-01-15 11:03:00', 'Product'),
    ('user_b', '2024-01-15 11:07:00', 'Product'),
    ('user_b', '2024-01-15 11:10:00', 'Home'),

    -- User C: Home -> Product -> Cart -> Home (cart abandonment)
    ('user_c', '2024-01-15 14:00:00', 'Home'),
    ('user_c', '2024-01-15 14:05:00', 'Product'),
    ('user_c', '2024-01-15 14:08:00', 'Cart'),
    ('user_c', '2024-01-15 14:15:00', 'Home'),

    -- User D: Home -> Product -> Checkout (skipped cart)
    ('user_d', '2024-01-15 15:00:00', 'Home'),
    ('user_d', '2024-01-15 15:02:00', 'Product'),
    ('user_d', '2024-01-15 15:05:00', 'Checkout');

Analytical Query: Forward Flow

-- What page do users visit after Home -> Product?
SELECT
    user_id,
    sequence_next_node(
        'forward',
        'first_match',
        event_time,
        page,
        page = 'Home',      -- base_condition: start from Home
        page = 'Home',      -- event1: match Home
        page = 'Product'    -- event2: then match Product
    ) AS next_page_after_product
FROM navigation_events
GROUP BY user_id
ORDER BY user_id;

Expected Results (Forward)

Analytical Query: Backward Flow

-- What page leads users to arrive at Checkout?
SELECT
    user_id,
    sequence_next_node(
        'backward',
        'first_match',
        event_time,
        page,
        page = 'Checkout',     -- base_condition: anchor on Checkout
        page = 'Checkout'      -- event1: match Checkout
    ) AS page_before_checkout
FROM navigation_events
WHERE user_id IN ('user_a', 'user_d')  -- only users who reached Checkout
GROUP BY user_id
ORDER BY user_id;

Expected Results (Backward)

Aggregate Flow Distribution

-- Distribution: what do users do after Home -> Product?
WITH next_pages AS (
    SELECT
        sequence_next_node(
            'forward',
            'first_match',
            event_time,
            page,
            page = 'Home',
            page = 'Home',
            page = 'Product'
        ) AS next_page
    FROM navigation_events
    GROUP BY user_id
)
SELECT
    COALESCE(next_page, '(end of session)') AS next_page,
    COUNT(*) AS user_count,
    ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS pct
FROM next_pages
GROUP BY next_page
ORDER BY user_count DESC;

Interpretation

The forward analysis reveals that after the Home-to-Product sequence:

• 50% of users proceed to Cart (users A and C) -- the intended happy path.
• 25% stay on Product pages (user B) -- likely comparing products.
• 25% go directly to Checkout (user D) -- possible express checkout behavior.

The backward analysis shows that the Cart page is the most common predecessor to Checkout, while some users skip the Cart entirely. This information helps the product team understand whether the Cart step adds friction or value to the conversion flow.

A/B Test Behavioral Analysis

Problem

A growth team is running an A/B test on a new onboarding flow. They need to compare behavioral patterns between the control and treatment groups. Beyond simple conversion rates, they want to measure: funnel depth, event sequence completion, and the number of times users repeat certain actions. This multi-dimensional behavioral comparison reveals whether the new flow changes user behavior patterns, not just outcomes.

Sample Data

CREATE TABLE ab_test_events (
    user_id     VARCHAR NOT NULL,
    event_time  TIMESTAMP NOT NULL,
    event_type  VARCHAR NOT NULL,
    test_group  VARCHAR NOT NULL   -- 'control' or 'treatment'
);

INSERT INTO ab_test_events VALUES
    -- Control group: User A - completes onboarding slowly
    ('user_a', '2024-01-15 10:00:00', 'signup',          'control'),
    ('user_a', '2024-01-15 10:05:00', 'profile_setup',   'control'),
    ('user_a', '2024-01-15 10:30:00', 'tutorial_start',  'control'),
    ('user_a', '2024-01-15 10:45:00', 'tutorial_end',    'control'),
    ('user_a', '2024-01-15 11:00:00', 'first_action',    'control'),

    -- Control group: User B - drops off after profile
    ('user_b', '2024-01-15 11:00:00', 'signup',          'control'),
    ('user_b', '2024-01-15 11:10:00', 'profile_setup',   'control'),
    ('user_b', '2024-01-15 11:15:00', 'tutorial_start',  'control'),

    -- Control group: User C - signs up only
    ('user_c', '2024-01-15 12:00:00', 'signup',          'control'),

    -- Treatment group: User D - completes onboarding quickly
    ('user_d', '2024-01-15 10:00:00', 'signup',          'treatment'),
    ('user_d', '2024-01-15 10:02:00', 'profile_setup',   'treatment'),
    ('user_d', '2024-01-15 10:05:00', 'tutorial_start',  'treatment'),
    ('user_d', '2024-01-15 10:08:00', 'tutorial_end',    'treatment'),
    ('user_d', '2024-01-15 10:10:00', 'first_action',    'treatment'),

    -- Treatment group: User E - completes onboarding
    ('user_e', '2024-01-15 13:00:00', 'signup',          'treatment'),
    ('user_e', '2024-01-15 13:03:00', 'profile_setup',   'treatment'),
    ('user_e', '2024-01-15 13:06:00', 'tutorial_start',  'treatment'),
    ('user_e', '2024-01-15 13:10:00', 'tutorial_end',    'treatment'),
    ('user_e', '2024-01-15 13:12:00', 'first_action',    'treatment'),

    -- Treatment group: User F - drops off after tutorial
    ('user_f', '2024-01-15 14:00:00', 'signup',          'treatment'),
    ('user_f', '2024-01-15 14:02:00', 'profile_setup',   'treatment'),
    ('user_f', '2024-01-15 14:04:00', 'tutorial_start',  'treatment'),
    ('user_f', '2024-01-15 14:07:00', 'tutorial_end',    'treatment');

Analytical Query: Multi-Dimensional Comparison

-- Dimension 1: Funnel depth (how far into onboarding?)
WITH funnel_analysis AS (
    SELECT
        user_id,
        test_group,
        window_funnel(
            INTERVAL '2 hours',
            event_time,
            event_type = 'signup',
            event_type = 'profile_setup',
            event_type = 'tutorial_start',
            event_type = 'tutorial_end',
            event_type = 'first_action'
        ) AS funnel_step
    FROM ab_test_events
    GROUP BY user_id, test_group
),
-- Dimension 2: Did the full sequence complete within 30 minutes?
sequence_analysis AS (
    SELECT
        user_id,
        test_group,
        sequence_match(
            '(?1).*(?t<=1800)(?2).*(?t<=1800)(?3).*(?t<=1800)(?4).*(?t<=1800)(?5)',
            event_time,
            event_type = 'signup',
            event_type = 'profile_setup',
            event_type = 'tutorial_start',
            event_type = 'tutorial_end',
            event_type = 'first_action'
        ) AS completed_within_30min
    FROM ab_test_events
    GROUP BY user_id, test_group
),
-- Dimension 3: Event timestamps for completed users
timing_analysis AS (
    SELECT
        user_id,
        test_group,
        sequence_match_events(
            '(?1).*(?2).*(?3).*(?4).*(?5)',
            event_time,
            event_type = 'signup',
            event_type = 'profile_setup',
            event_type = 'tutorial_start',
            event_type = 'tutorial_end',
            event_type = 'first_action'
        ) AS step_timestamps
    FROM ab_test_events
    GROUP BY user_id, test_group
)
-- Combined report per test group
SELECT
    f.test_group,
    COUNT(*) AS users,
    ROUND(AVG(f.funnel_step), 2) AS avg_funnel_depth,
    SUM(CASE WHEN f.funnel_step = 5 THEN 1 ELSE 0 END) AS completed_onboarding,
    ROUND(100.0 * SUM(CASE WHEN f.funnel_step = 5 THEN 1 ELSE 0 END) /
        COUNT(*), 1) AS completion_rate_pct,
    SUM(CASE WHEN s.completed_within_30min THEN 1 ELSE 0 END)
        AS completed_fast,
    ROUND(100.0 * SUM(CASE WHEN s.completed_within_30min THEN 1 ELSE 0 END) /
        NULLIF(SUM(CASE WHEN f.funnel_step = 5 THEN 1 ELSE 0 END), 0), 1)
        AS fast_completion_rate_pct
FROM funnel_analysis f
JOIN sequence_analysis s ON f.user_id = s.user_id
JOIN timing_analysis t ON f.user_id = t.user_id
GROUP BY f.test_group
ORDER BY f.test_group;

Expected Results

Step-by-Step Funnel Comparison

-- Detailed funnel step distribution per group
WITH user_funnels AS (
    SELECT
        user_id,
        test_group,
        window_funnel(
            INTERVAL '2 hours',
            event_time,
            event_type = 'signup',
            event_type = 'profile_setup',
            event_type = 'tutorial_start',
            event_type = 'tutorial_end',
            event_type = 'first_action'
        ) AS step
    FROM ab_test_events
    GROUP BY user_id, test_group
)
SELECT
    test_group,
    step AS reached_step,
    COUNT(*) AS user_count
FROM user_funnels
GROUP BY test_group, step
ORDER BY test_group, step;

Interpretation

The treatment group shows stronger funnel metrics; per-step speed does not separate the groups in this sample:

• Funnel depth: Average 4.67 steps vs. 3.00 -- users progress further through onboarding.
• Completion rate: 66.7% vs. 33.3% -- twice as many users complete the full onboarding.
• Speed: Every completer in both groups moved through each step within 30 minutes, so fast_completion_rate_pct is 100.0 for both. The single control completer (user A) took 60 minutes overall, but its largest gap (25 minutes, between profile setup and tutorial start) is still within the 30-minute per-step limit; the treatment completers took 10 and 12 minutes overall.
• Drop-off pattern: In the control group, one user (C) dropped off at step 1 (signup only), suggesting the immediate post-signup experience needs work. In the treatment group, the only non-completing user (F) still reached step 4 (tutorial_end), indicating better engagement even among non-completers.

The sequence_match with time constraints ((?t<=1800)) specifically identifies users who moved through each step within 30 minutes, measuring momentum rather than just completion. The sequence_match_events output can be used for further analysis of time-between-steps to identify specific bottleneck transitions.

SQL Cookbook

Practical recipes for common behavioral analytics patterns. Each recipe is self-contained — copy and paste into a DuckDB session with the extension loaded.

Funnel Recipes

Basic Conversion Funnel

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

Funnel Drop-off Report

Aggregate per-user funnel results into a conversion report showing where users abandon:

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;

Funnel by Date

Track daily funnel conversion rates:

SELECT
  event_time::DATE as day,
  COUNT(*) as total_users,
  SUM(CASE WHEN step >= 1 THEN 1 ELSE 0 END) as viewed,
  SUM(CASE WHEN step >= 2 THEN 1 ELSE 0 END) as carted,
  SUM(CASE WHEN step >= 3 THEN 1 ELSE 0 END) as purchased,
  ROUND(100.0 * SUM(CASE WHEN step >= 3 THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN step >= 1 THEN 1 ELSE 0 END), 0), 1) as conversion_pct
FROM (
  SELECT user_id, MIN(event_time) as event_time,
    window_funnel(INTERVAL '1 hour', event_time,
      event_type = 'page_view',
      event_type = 'add_to_cart',
      event_type = 'purchase'
    ) as step
  FROM events
  GROUP BY user_id
)
GROUP BY day
ORDER BY day;

Strict Funnel (No Repeated Steps)

Use strict_increase mode to require strictly increasing timestamps between steps — no duplicate timestamps allowed:

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

Funnel with Re-entry

Allow the funnel to restart when the first condition fires again, tracking the best attempt:

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

Funnel by Segment

Compare funnel performance across user segments (device, campaign, etc.):

SELECT
  device_type,
  COUNT(*) as users,
  ROUND(AVG(step), 2) as avg_step,
  SUM(CASE WHEN step = 4 THEN 1 ELSE 0 END) as completed,
  ROUND(100.0 * SUM(CASE WHEN step = 4 THEN 1 ELSE 0 END) / COUNT(*), 1) as pct
FROM (
  SELECT user_id, device_type,
    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, device_type
)
GROUP BY device_type
ORDER BY pct DESC;

Session Recipes

Basic Session Assignment

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;

Session Metrics (Duration, Page Count, Bounce Rate)

WITH sessionized AS (
  SELECT user_id, event_time, page_url,
    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 started_at,
  MAX(event_time) as ended_at,
  EXTRACT(EPOCH FROM MAX(event_time) - MIN(event_time)) as duration_sec,
  CASE WHEN COUNT(*) = 1 THEN true ELSE false END as is_bounce
FROM sessionized
GROUP BY user_id, session_id;

Sessions Per User Per Day

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,
  event_time::DATE as day,
  COUNT(DISTINCT session_id) as sessions
FROM sessionized
GROUP BY user_id, day
ORDER BY user_id, day;

Average Session Duration by Day

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
),
session_stats AS (
  SELECT
    user_id, session_id,
    MIN(event_time)::DATE as day,
    EXTRACT(EPOCH FROM MAX(event_time) - MIN(event_time)) as duration_sec
  FROM sessionized
  GROUP BY user_id, session_id
)
SELECT
  day,
  COUNT(*) as sessions,
  ROUND(AVG(duration_sec), 0) as avg_duration_sec,
  ROUND(AVG(duration_sec) / 60.0, 1) as avg_duration_min
FROM session_stats
GROUP BY day
ORDER BY day;

Entry Page Analysis

Identify the first page of each session for channel attribution:

WITH sessionized AS (
  SELECT user_id, event_time, page_url, referrer,
    sessionize(event_time, INTERVAL '30 minutes') OVER (
      PARTITION BY user_id ORDER BY event_time
    ) as session_id
  FROM events
)
SELECT
  page_url as entry_page,
  COUNT(*) as sessions,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) as pct
FROM (
  SELECT DISTINCT ON (user_id, session_id) user_id, session_id, page_url, referrer
  FROM sessionized
  ORDER BY user_id, session_id, event_time
)
GROUP BY page_url
ORDER BY sessions DESC;

Retention Recipes

Weekly Cohort Retention

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,
  SUM(CASE WHEN r[4] THEN 1 ELSE 0 END) as week_3,
  ROUND(100.0 * SUM(CASE WHEN r[2] THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN r[1] THEN 1 ELSE 0 END), 0), 1) as w1_pct,
  ROUND(100.0 * SUM(CASE WHEN r[3] THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN r[1] THEN 1 ELSE 0 END), 0), 1) as w2_pct,
  ROUND(100.0 * SUM(CASE WHEN r[4] THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN r[1] THEN 1 ELSE 0 END), 0), 1) as w3_pct
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',
      activity_date >= cohort_week + INTERVAL '21 days' AND activity_date < cohort_week + INTERVAL '28 days'
    ) as r
  FROM activity GROUP BY user_id, cohort_week
)
GROUP BY cohort_week
ORDER BY cohort_week;

Day-1 / Day-7 / Day-30 Retention

Classic mobile/SaaS retention metrics:

SELECT
  signup_date,
  COUNT(*) as new_users,
  SUM(CASE WHEN r[1] THEN 1 ELSE 0 END) as day_0,
  ROUND(100.0 * SUM(CASE WHEN r[2] THEN 1 ELSE 0 END) /
    NULLIF(COUNT(*), 0), 1) as d1_pct,
  ROUND(100.0 * SUM(CASE WHEN r[3] THEN 1 ELSE 0 END) /
    NULLIF(COUNT(*), 0), 1) as d7_pct,
  ROUND(100.0 * SUM(CASE WHEN r[4] THEN 1 ELSE 0 END) /
    NULLIF(COUNT(*), 0), 1) as d30_pct
FROM (
  SELECT user_id, signup_date,
    retention(
      activity_date = signup_date,
      activity_date = signup_date + INTERVAL '1 day',
      activity_date = signup_date + INTERVAL '7 days',
      activity_date = signup_date + INTERVAL '30 days'
    ) as r
  FROM user_activity GROUP BY user_id, signup_date
)
GROUP BY signup_date
ORDER BY signup_date;

Retention by Segment

Compare retention across user segments (plan type, acquisition channel, etc.):

SELECT
  plan_type,
  COUNT(*) as users,
  ROUND(100.0 * SUM(CASE WHEN r[2] THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN r[1] THEN 1 ELSE 0 END), 0), 1) as w1_retention,
  ROUND(100.0 * SUM(CASE WHEN r[3] THEN 1 ELSE 0 END) /
    NULLIF(SUM(CASE WHEN r[1] THEN 1 ELSE 0 END), 0), 1) as w2_retention
FROM (
  SELECT user_id, plan_type,
    retention(
      activity_week = signup_week,
      activity_week = signup_week + INTERVAL '7 days',
      activity_week = signup_week + INTERVAL '14 days'
    ) as r
  FROM user_activity GROUP BY user_id, plan_type
)
GROUP BY plan_type
ORDER BY w1_retention DESC;

Sequence Pattern Recipes

Detect a Specific Event Sequence

Did the user view a product then add it to cart (with any events in between)?

SELECT user_id,
  sequence_match('(?1).*(?2)', event_time,
    event_type = 'page_view',
    event_type = 'add_to_cart'
  ) as viewed_then_carted
FROM events GROUP BY user_id;

Time-Constrained Pattern

User signed up and completed onboarding within 10 minutes:

SELECT user_id,
  sequence_match('(?1).*(?t<=600)(?2)', event_time,
    event_type = 'signup',
    event_type = 'onboarding_complete'
  ) as fast_onboarder
FROM events GROUP BY user_id;

Count Repeated Patterns

How many times does each user repeat the browse → cart cycle?

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

Multi-Step Pattern with Wildcards

Detect users who viewed, added to cart, then purchased — but NOT immediately (at least one event between cart and purchase):

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

Get Matched Timestamps

Retrieve the exact timestamps when each step in a 3-step pattern matched:

SELECT user_id,
  sequence_match_events('(?1).*(?2).*(?3)', event_time,
    event_type = 'signup',
    event_type = 'first_purchase',
    event_type = 'second_purchase'
  ) as milestone_times
FROM events GROUP BY user_id;

Time Between Pattern Steps

Calculate the time between matched pattern steps:

WITH matched AS (
  SELECT user_id,
    sequence_match_events('(?1).*(?2).*(?3)', event_time,
      event_type = 'signup',
      event_type = 'first_purchase',
      event_type = 'review'
    ) as ts
  FROM events GROUP BY user_id
)
SELECT user_id,
  ts[1] as signup_time,
  ts[2] as purchase_time,
  ts[3] as review_time,
  EXTRACT(EPOCH FROM ts[2] - ts[1]) / 3600.0 as hours_to_purchase,
  EXTRACT(EPOCH FROM ts[3] - ts[2]) / 3600.0 as hours_to_review
FROM matched
WHERE len(ts) = 3;

User Flow Recipes

Forward Flow — What Happens Next?

After Home → Product, what do users do next?

SELECT
  COALESCE(next_page, '(end of session)') as next_page,
  COUNT(*) as users,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) as pct
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 users DESC;

Backward Flow — What Led Here?

What page do users visit immediately before reaching the Checkout page?

SELECT
  COALESCE(prev_page, '(start of session)') as prev_page,
  COUNT(*) as users,
  ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) as pct
FROM (
  SELECT user_id,
    sequence_next_node('backward', 'first_match', event_time, page,
      page = 'Checkout', page = 'Checkout'
    ) as prev_page
  FROM events GROUP BY user_id
)
GROUP BY prev_page
ORDER BY users DESC;

Last-Match Flow

With last_match, the anchor is the last event satisfying both the base condition and event1 (here: the user's last Home view). The chain must then match consecutive events (Home, then Product immediately after), and the result is the page after that Product view. If the event right after the last Home view is not Product, the result is NULL; an earlier Home → Product occurrence is not used as a fallback:

SELECT user_id,
  sequence_next_node('forward', 'last_match', event_time, page,
    page = 'Home', page = 'Home', page = 'Product'
  ) as next_page_after_last
FROM events GROUP BY user_id;

Combined Analysis Recipes

A/B Test Behavioral Comparison

Compare funnel depth and conversion speed between test groups:

WITH funnel AS (
  SELECT user_id, test_group,
    window_funnel(INTERVAL '2 hours', event_time,
      event_type = 'signup',
      event_type = 'profile_setup',
      event_type = 'first_action'
    ) as step
  FROM events GROUP BY user_id, test_group
),
speed AS (
  SELECT user_id, test_group,
    sequence_match('(?1).*(?t<=1800)(?2).*(?t<=1800)(?3)', event_time,
      event_type = 'signup',
      event_type = 'profile_setup',
      event_type = 'first_action'
    ) as completed_fast
  FROM events GROUP BY user_id, test_group
)
SELECT
  f.test_group,
  COUNT(*) as users,
  ROUND(AVG(f.step), 2) as avg_depth,
  SUM(CASE WHEN f.step = 3 THEN 1 ELSE 0 END) as completed,
  ROUND(100.0 * SUM(CASE WHEN f.step = 3 THEN 1 ELSE 0 END) / COUNT(*), 1) as complete_pct,
  SUM(CASE WHEN s.completed_fast THEN 1 ELSE 0 END) as fast_completers
FROM funnel f
JOIN speed s ON f.user_id = s.user_id
GROUP BY f.test_group;

Session + Funnel Combined

Analyze funnel performance per session:

WITH sessionized AS (
  SELECT *, 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,
  window_funnel(INTERVAL '1 hour', event_time,
    event_type = 'page_view',
    event_type = 'add_to_cart',
    event_type = 'purchase'
  ) as step
FROM sessionized
GROUP BY user_id, session_id
ORDER BY user_id, session_id;

Power Users Detection

Find users with the most repeated behavioral patterns:

SELECT user_id,
  sequence_count('(?1).*(?2)', event_time,
    event_type = 'search',
    event_type = 'page_view'
  ) as search_browse_cycles,
  sequence_count('(?1).*(?2)', event_time,
    event_type = 'page_view',
    event_type = 'add_to_cart'
  ) as browse_cart_cycles,
  window_funnel(INTERVAL '24 hours', event_time,
    event_type = 'page_view',
    event_type = 'add_to_cart',
    event_type = 'purchase'
  ) as funnel_depth
FROM events
GROUP BY user_id
ORDER BY browse_cart_cycles DESC
LIMIT 20;

Querying Parquet Files Directly

All recipes work with any DuckDB-supported file format:

SELECT user_id,
  window_funnel(INTERVAL '1 hour', event_time,
    event_type = 'view', event_type = 'cart', event_type = 'purchase'
  ) as step
FROM read_parquet('s3://my-bucket/events/*.parquet')
WHERE event_time >= '2024-01-01'
GROUP BY user_id;

Pattern Syntax Quick Reference

Common patterns:

(?1).*(?2)              -- cond1 then cond2, any gap
(?1)(?2)                -- cond1 immediately followed by cond2
(?1).*(?t<=3600)(?2)    -- cond1 then cond2 within 1 hour
(?1).(?2)               -- cond1, one event, then cond2
(?1).*(?2).*(?3)        -- three-step sequence

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:

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):

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

Common Patterns

NULL Handling

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

ClickHouse Translation

FAQ

Frequently asked questions about duckdb-behavioral.

Loading the Extension

How do I install the extension?

The extension is available in the DuckDB Community Extensions repository. Install and load with:

INSTALL behavioral FROM community;
LOAD behavioral;

No build tools, compilation, or -unsigned flag required.

Can I build from source instead?

Yes. DuckDB only loads files ending in .duckdb_extension that carry its metadata footer, so build in release mode and append the footer first:

cargo build --release
git submodule update --init --recursive   # first time only
cp target/release/libbehavioral.so /tmp/behavioral.duckdb_extension   # .dylib on macOS
python3 extension-ci-tools/scripts/append_extension_metadata.py \
  -l /tmp/behavioral.duckdb_extension -n behavioral \
  -p linux_amd64 -dv v1.2.0 -ev v0.10.1 \
  -o /tmp/behavioral.duckdb_extension

Locally built extensions are unsigned, so load them with -unsigned:

duckdb -unsigned -c "LOAD '/tmp/behavioral.duckdb_extension'; SELECT behavioral_version();"

make configure release does the same through DuckDB's extension-ci-tools and writes build/release/behavioral.duckdb_extension. See Getting Started for the platform names.

The extension fails to load. What should I check?

1. DuckDB version mismatch: The community repository publishes a build per DuckDB release; a release newer than the last community build may not have one yet. A locally built extension is stamped for the stable C API (-dv v1.2.0, ABI type C_STRUCT) and loads into any DuckDB release with that C API or newer; stamping it C_STRUCT_UNSTABLE would pin it to the one release named by -dv.

2. Missing -unsigned flag (local builds only): DuckDB rejects unsigned extensions by default. Use duckdb -unsigned or set allow_unsigned_extensions=true. This does not apply when installing via INSTALL behavioral FROM community.

3. Wrong file path (local builds only): Point LOAD at the stamped .duckdb_extension file (build/release/behavioral.duckdb_extension from make configure release, or the file you ran append_extension_metadata.py on). DuckDB refuses the raw .so/.dylib from cargo build --release ("DuckDB extensions are files ending with '.duckdb_extension'").

4. Platform mismatch (local builds only): An extension built on Linux cannot be loaded on macOS, and vice versa. The community extension handles platform selection automatically.

Functions

What is the difference between sequence_match, sequence_count, and sequence_match_events?

All three use the same pattern syntax and matching engine, but return different results:

Use sequence_match for filtering, sequence_count for frequency analysis, and sequence_match_events for diagnostic investigation of when each step was satisfied.

How many boolean conditions can I use?

The conditions are stored internally as a u32 bitmask, so 32 is a hard limit of the data type. ClickHouse's windowFunnel and sequenceMatch also stop at 32; its sequenceNextNode allows up to 64 event conditions.

How are NULL values handled?

• NULL timestamps: Rows with NULL timestamps are ignored during update.
• NULL conditions: NULL boolean conditions are treated as false.
• NULL pattern: sequence_match and sequence_count return NULL; sequence_match_events returns an empty list. An empty pattern string is malformed and raises an error.
• NULL configuration (other than the pattern): a NULL window_funnel window skips that row; a NULL mode means no mode; a NULL sequence_next_node direction is treated as 'forward' and a NULL base as 'first_match'.
• sequence_next_node: NULL event column values are stored and can be returned as the result. The function returns NULL when no match is found or no adjacent event exists.

When should I use window_funnel vs. sequence_match?

Both functions analyze multi-step user journeys, but they serve different purposes:

• window_funnel answers: "How far did the user get through a specific ordered funnel within a time window?" It returns a step count (0 to N) and supports behavioral modes (strict, strict_once, etc.). Best for conversion funnel analysis where you want drop-off rates between steps.

• sequence_match answers: "Did a specific pattern of events occur?" It uses a flexible regex-like syntax supporting wildcards (., .*) and time constraints ((?t<=3600)). Best for detecting complex behavioral patterns that go beyond simple ordered steps.

Use window_funnel when you need step-by-step drop-off metrics. Use sequence_match when you need flexible pattern matching with arbitrary gaps and time constraints.

Pattern Syntax

Quick reference

Common patterns

-- User viewed then purchased (any events in between)
'(?1).*(?2)'

-- User viewed then purchased with no intervening events
'(?1)(?2)'

-- User viewed, then purchased within 1 hour (3600 seconds)
'(?1).*(?t<=3600)(?2)'

-- Three-step funnel with time constraints
'(?1).*(?t<=3600)(?2).*(?t<=7200)(?3)'

Window Funnel Modes

What modes are available and how do they combine?

Five ClickHouse-compatible modes plus one extension mode are available, each adding an independent constraint:

Modes are independently combinable via a comma-separated string:

window_funnel(INTERVAL '1 hour', 'strict_increase, strict_once',
  ts, cond1, cond2, cond3)

Differences from ClickHouse

How does the syntax differ from ClickHouse?

ClickHouse uses a two-level call syntax where configuration parameters are separated from data parameters by an extra set of parentheses. duckdb-behavioral uses a flat parameter list, consistent with standard SQL function syntax.

-- ClickHouse syntax
windowFunnel(3600)(timestamp, cond1, cond2, cond3)
sequenceMatch('(?1).*(?2)')(timestamp, cond1, cond2)
sequenceNextNode('forward', 'head')(timestamp, value, base_cond, ev1, ev2)

-- duckdb-behavioral syntax
window_funnel(INTERVAL '1 hour', timestamp, cond1, cond2, cond3)
sequence_match('(?1).*(?2)', timestamp, cond1, cond2)
sequence_next_node('forward', 'head', timestamp, value, base_cond, ev1, ev2)

What are the naming convention differences?

ClickHouse uses camelCase. duckdb-behavioral uses snake_case, following DuckDB's naming conventions.

Are there differences in parameter types?

The INTERVAL type is more expressive than raw seconds. You can write INTERVAL '1 hour', INTERVAL '30 minutes', or INTERVAL '2 days' instead of computing the equivalent number of seconds.

Does this extension have a sessionize equivalent in ClickHouse?

No. The sessionize function has no direct equivalent in ClickHouse's behavioral analytics function set. It is an addition in this extension for assigning session IDs based on inactivity gaps, designed to be used as a window aggregate with OVER (PARTITION BY ... ORDER BY ...).

Do I need to set any experimental flags?

No. In ClickHouse, sequenceNextNode requires SET allow_experimental_funnel_functions = 1. In duckdb-behavioral, all eight functions are available immediately after loading the extension, with no experimental flags required.

Data Preparation

What columns does my data need?

The required columns depend on which function you use:

The boolean "condition" parameters are typically inline expressions rather than stored columns. For example:

-- Conditions are computed inline from existing columns
window_funnel(INTERVAL '1 hour', event_time,
  event_type = 'page_view',       -- cond1: computed from event_type
  event_type = 'add_to_cart',     -- cond2: computed from event_type
  event_type = 'purchase'         -- cond3: computed from event_type
)

What is the ideal table structure for behavioral analytics?

A single event-level table with one row per user action works best. The minimum structure is:

CREATE TABLE events (
    user_id    VARCHAR NOT NULL,   -- or INTEGER, UUID, etc.
    event_time TIMESTAMP NOT NULL,
    event_type VARCHAR NOT NULL    -- the action taken
);

For richer analysis, add context columns:

CREATE TABLE events (
    user_id      VARCHAR NOT NULL,
    event_time   TIMESTAMP NOT NULL,
    event_type   VARCHAR NOT NULL,
    page_url     VARCHAR,            -- for web analytics
    product_id   VARCHAR,            -- for e-commerce
    revenue      DECIMAL(10,2),      -- for purchase events
    device_type  VARCHAR,            -- for segmentation
    campaign     VARCHAR             -- for attribution
);

All behavioral functions operate on this flat event stream. There is no need for pre-aggregated or pivoted data.

Do events need to be sorted before calling these functions?

No. All event-collecting functions (window_funnel, sequence_match, sequence_count, sequence_match_events, sequence_next_node) sort events by timestamp internally during the finalize phase. You do not need an ORDER BY clause for these aggregate functions.

Do not put an ORDER BY inside the function call (window_funnel(... ORDER BY ts)): DuckDB runs such ordered aggregates through a code path that crashes every C API aggregate, this extension's included (see Which query shapes crash DuckDB?). It is never needed, because every function sorts by timestamp itself.

When events already arrive in timestamp order, a presorted check skips the O(n log n) sort, reducing finalize to O(n).

sessionize is the exception: it is an aggregate meant to be used with OVER (... ORDER BY ...), and the ORDER BY in the OVER clause is what makes it assign a running session ID per row (without OVER it returns the number of sessions in the group):

sessionize(event_time, INTERVAL '30 minutes') OVER (
    PARTITION BY user_id ORDER BY event_time
)

Can I use these functions with Parquet, CSV, or other file formats?

Yes. DuckDB can read from Parquet, CSV, JSON, and many other formats directly. The behavioral functions operate on DuckDB's internal columnar representation, so the source format is irrelevant:

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

-- Query CSV files
SELECT user_id,
  retention(
    event_date = '2024-01-01',
    event_date = '2024-01-02',
    event_date = '2024-01-03')
FROM read_csv('events.csv')
GROUP BY user_id;

Scaling and Memory

How much data can the extension handle?

The Rust aggregate state has been benchmarked at scale with Criterion.rs. These are microbenchmarks of update/combine/finalize, not end-to-end SQL queries, taken from PERF.md Session 15 (before v0.8.0, not re-measured since):

In practice, real-world datasets with billions of rows are easily handled because the functions operate on partitioned groups (e.g., per-user), not the entire table at once. A table with 1 billion rows across 10 million users has only 100 events per user on average.

How much memory do the functions use?

Memory usage depends on the function category:

O(1)-state functions (constant memory regardless of group size):

• sessionize: Tracks only first_ts, last_ts, and boundaries count. Requires a few dozen bytes per partition segment, regardless of how many events exist.
• retention: Stores a single u32 bitmask (4 bytes) per group. One billion rows with retention requires effectively zero additional memory.

Event-collecting functions (linear memory proportional to group size):

• window_funnel, sequence_match, sequence_count, sequence_match_events: Store every event as a 16-byte Event struct. For a group with 10,000 events, this requires approximately 160 KB.
• sequence_next_node: Stores every event as a 32-byte NextNodeEvent struct (includes an Arc<str> reference to the value column). For a group with 10,000 events, this requires approximately 320 KB plus string storage.

Rule of thumb: For event-collecting functions, estimate memory as 16 bytes * (events in largest group) for most functions, or 32 bytes * (events in largest group) for sequence_next_node. The group is defined by GROUP BY for aggregate functions or PARTITION BY for sessionize.

What happens if a single user has millions of events?

Memory scales linearly with the number of events in that group: a single user with 10 million events needs about 160 MB for the event-collecting functions (16 bytes per event). While finalizing, sequence_* patterns that use more than conditions and .* also need about 8 bytes per event, plus one bit per event for each (?N) or . step; patterns are capped at 1024 steps. This memory is not counted against DuckDB's memory_limit; an allocation that fails raises an out of memory error instead of crashing.

Time is linear for window_funnel, retention, sessionize, sequence_next_node, and for sequence_* patterns built only from conditions and .*. Other sequence_* patterns (time constraints, ., adjacent conditions mixed with .*) take O(s · n log n) for s pattern steps. With one group of 10 million events, sequence_count took 0.78–0.90 s for (?1).*(?t<5)(?2).*(?3) and for (?1)(?t>=5)(?3) (DuckDB 1.5.6, 3 runs each). Through v0.9.1 these patterns used a backtracking search that was quadratic when the pattern did not complete: 8.3–9.1 s at 32,000 events, and 32–35 s for sequence_match_events. DuckDB's C API gives an aggregate no way to observe an interrupt, so a query cannot be cancelled while one group is being finalized.

If you have users with extremely large event counts, consider pre-filtering to a relevant time window before applying behavioral functions:

-- Pre-filter to last 90 days before funnel analysis
SELECT user_id,
  window_funnel(INTERVAL '1 hour', event_time,
    event_type = 'view', event_type = 'cart', event_type = 'purchase')
FROM events
WHERE event_time >= CURRENT_TIMESTAMP - INTERVAL '90 days'
GROUP BY user_id;

Does the extension support out-of-core processing?

The extension itself does not implement out-of-core (disk-spilling) strategies. However, DuckDB's query engine handles memory management for the overall query pipeline. The aggregate state for each group is held in memory during execution. For most workloads where GROUP BY partitions data into reasonably sized groups (thousands to tens of thousands of events per user), this is not a concern.

Combine and Aggregate Behavior

How does GROUP BY work with these functions?

The aggregate functions (retention, window_funnel, sequence_match, sequence_count, sequence_match_events, sequence_next_node) follow standard SQL GROUP BY semantics. Each group produces one output row:

-- One result per user
SELECT user_id,
  window_funnel(INTERVAL '1 hour', event_time,
    event_type = 'view', event_type = 'purchase') as steps
FROM events
GROUP BY user_id;

-- One result per user per day
SELECT user_id, event_time::DATE as day,
  sequence_match('(?1).*(?2)', event_time,
    event_type = 'view', event_type = 'purchase') as converted
FROM events
GROUP BY user_id, day;

If you omit GROUP BY, the function aggregates over the entire table (all rows form a single group):

-- Single result for the entire table
SELECT retention(
    event_date = '2024-01-01',
    event_date = '2024-01-02'
  ) as overall_retention
FROM events;

What is the combine operation and why does it matter?

DuckDB processes aggregate functions using a segment tree, which requires merging partial aggregate states. The combine operation merges two states into one. For event-collecting functions, this means appending one state's events to another's.

This is an internal implementation detail that you do not need to worry about for correctness. However, it affects performance:

• sessionize and retention have O(1) combine (constant time regardless of data size), making them extremely fast even at billion-row scale.
• Event-collecting functions have O(m) combine where m is the number of events in the source state. For scale: in the Criterion microbenchmarks, updating and finalizing a single 100-million-event state takes 791 ms for window_funnel and 1.05-1.18 s for the sequence_* functions; with many partial states, combine becomes the dominant cost.

Can I use these functions with PARTITION BY (window functions)?

Yes. DuckDB can run any aggregate as a window function, and sessionize is designed to be used that way:

SELECT sessionize(event_time, INTERVAL '30 minutes') OVER (
    PARTITION BY user_id ORDER BY event_time
) as session_id
FROM events;

The other functions are normally used with GROUP BY:

SELECT user_id,
  window_funnel(INTERVAL '1 hour', event_time, cond1, cond2)
FROM events
GROUP BY user_id;

They also work over running or sliding frames, for example OVER (PARTITION BY user_id ORDER BY event_time) gives a running funnel step per row; over large partitions that is slow (see Why is my windowed query slow). Avoid OVER () and UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frames; see the next question.

Which query shapes crash DuckDB?

A defect in DuckDB's C API for aggregate functions (duckdb/duckdb#26109) makes DuckDB read out of bounds, and usually crash with a segmentation fault, when any aggregate registered through the C API, including every function in this extension, is called in these shapes:

• ORDER BY inside the call, such as retention(c1, c2 ORDER BY ts). Drop it: every function sorts by timestamp itself.
• An empty window, OVER (). Use OVER (PARTITION BY 1) instead.
• A frame written BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, with ROWS or RANGE. Use OVER (PARTITION BY u) with no ORDER BY, which covers the whole partition without crashing.

Verified on DuckDB 1.5.6, where all three exit with a segmentation fault (and on 1.3.2 and 1.4.4). On DuckDB 2.0.0 none of them crashed for any of the eight functions (100,000 rows in one partition), and the seven event functions returned the same values as the plain aggregate, so the workarounds matter only for older releases. The safe alternatives return the same values as the equivalent GROUP BY (0 mismatches over 50 partitions of 400 rows). Other frames that cover the whole partition do not crash: OVER (PARTITION BY u), ROWS BETWEEN 100000 PRECEDING AND 100000 FOLLOWING, and ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING all ran cleanly.

The extension cannot detect or refuse the crashing shapes: DuckDB passes a one-element state array while reporting several rows, and the C API has no bind hook through which an aggregate could reject the query. Valgrind showed no invalid reads for GROUP BY, FILTER, DISTINCT, running frames (OVER (... ORDER BY ts)), sliding frames, EXCLUDE, or OVER (PARTITION BY u).

A whole-partition value can also be computed with GROUP BY and joined back:

WITH f AS (
  SELECT user_id, window_funnel(INTERVAL '1 hour', ts, c1, c2) AS step
  FROM events GROUP BY user_id
)
SELECT e.*, f.step FROM events e JOIN f USING (user_id);

Why is my windowed query slow or using a lot of memory?

Over a running frame (OVER (ORDER BY ts)) every row is a separate aggregate over all rows before it, so the event-collecting functions (window_funnel, window_funnel_events, the sequence_* functions, sequence_next_node) do work quadratic in the partition size. DuckDB also keeps about 2,048 of those frames in memory at once. For window_funnel over one 3-condition partition (DuckDB 1.5.6, one thread; a re-check on DuckDB 2.0.0 showed the same growth and the same peak memory, 1.24 GB at 40,000 rows):

This is inherent to running frames through DuckDB's C API, which has no window-specific callback. Partition by user (PARTITION BY user_id ORDER BY ts) so each partition stays small, bound the frame (ROWS BETWEEN 1000 PRECEDING AND CURRENT ROW; the cost is then proportional to rows times frame width), or use GROUP BY when one value per user is enough. sessionize is not affected: its state is a few integers.

Can I nest these functions or use them in subqueries?

Yes. The results of behavioral functions can be used in subqueries, CTEs, and outer queries like any other SQL expression:

-- Use window_funnel results in a downstream aggregation
WITH user_funnels AS (
    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
)
SELECT steps, COUNT(*) as user_count
FROM user_funnels
GROUP BY steps
ORDER BY steps;

Integration

How do I use this extension with Python?

Use the duckdb Python package. Install and load the extension from the community repository:

import duckdb

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

# Run behavioral queries and get results as a DataFrame
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()

You can also pass pandas DataFrames directly to DuckDB:

import pandas as pd

events_df = pd.DataFrame({
    'user_id': ['u1', 'u1', 'u1', 'u2', 'u2'],
    'event_time': pd.to_datetime([
        '2024-01-01 10:00', '2024-01-01 10:30', '2024-01-01 11:00',
        '2024-01-01 09:00', '2024-01-01 09:15'
    ]),
    'event_type': ['view', 'cart', 'purchase', 'view', 'cart']
})

result = 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_df
    GROUP BY user_id
""").fetchdf()

How do I use this extension with Node.js?

Use the duckdb-async or duckdb npm package:

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

db.run("INSTALL behavioral FROM community", (err) => {
    if (err) throw err;
    db.run("LOAD behavioral", (err) => {
        if (err) throw err;

        db.all(`
            SELECT user_id,
              window_funnel(INTERVAL '1 hour', event_time,
                event_type = 'view',
                event_type = 'cart',
                event_type = 'purchase') as steps
            FROM read_parquet('events.parquet')
            GROUP BY user_id
        `, (err, rows) => {
            console.log(rows);
        });
    });
});

How do I use this extension with dbt?

dbt-duckdb supports loading community extensions. Add the extension to your profiles.yml:

my_project:
  target: dev
  outputs:
    dev:
      type: duckdb
      path: ':memory:'
      extensions:
        - name: behavioral
          repo: community

Then use the behavioral functions directly in your dbt models:

-- models/staging/stg_user_funnels.sql
SELECT
    user_id,
    window_funnel(
        INTERVAL '1 hour',
        event_time,
        event_type = 'view',
        event_type = 'cart',
        event_type = 'purchase'
    ) as furthest_step
FROM {{ ref('stg_events') }}
GROUP BY user_id

-- models/staging/stg_user_sessions.sql
SELECT
    user_id,
    event_time,
    sessionize(event_time, INTERVAL '30 minutes') OVER (
        PARTITION BY user_id ORDER BY event_time
    ) as session_id
FROM {{ ref('stg_events') }}

Can I use the extension in DuckDB's WASM or HTTP client?

No. The extension is a native loadable binary (.so on Linux, .dylib on macOS) and requires the native DuckDB runtime. It cannot be loaded in DuckDB-WASM (browser) or through the DuckDB HTTP API without a native DuckDB server process backing the connection.

Performance

How fast is the extension?

Headline microbenchmarks of the Rust aggregate state (Criterion.rs, 95% CI, PERF.md Session 15, recorded before v0.8.0 and not re-measured; these are not end-to-end SQL timings):

See the Performance page for full methodology and optimization history.

Can I run the benchmarks myself?

Yes:

cargo bench                    # Run all benchmarks
cargo bench -- sessionize      # Run a specific group
cargo bench -- sequence_match  # Another specific group

Results are stored in target/criterion/ and automatically compared against previous runs.

What can I do to improve query performance?

1. Pre-filter by time range. Reduce the number of events before applying behavioral functions. A WHERE event_time >= '2024-01-01' clause can dramatically reduce the working set.

2. Use GROUP BY to keep group sizes reasonable. Partitioning by user_id ensures each group contains only one user's events rather than the entire table.

3. Don't add ORDER BY for the functions' sake. Every event-collecting function sorts by timestamp itself, and when rows already arrive in timestamp order (for example, from a file written in that order) a presorted check skips the O(n log n) sort. Never put ORDER BY inside the call (window_funnel(... ORDER BY ts)): it crashes DuckDB (see Which query shapes crash DuckDB?).

4. Use Parquet format. Parquet's columnar storage and predicate pushdown work well with DuckDB's query optimizer, reducing I/O for behavioral queries that typically read only a few columns.

sessionize

Window function that assigns monotonically increasing session IDs. A new session begins when the gap between consecutive events exceeds a configurable threshold.

Signature

sessionize(timestamp TIMESTAMP, gap INTERVAL) -> BIGINT

Parameters:

Returns: BIGINT -- the session ID (1-indexed, monotonically increasing within each partition).

Usage

sessionize is an aggregate designed to be used as a window function with OVER (PARTITION BY ... ORDER BY timestamp). Order the window by the timestamp, ascending, and keep the default frame (or any frame ending at CURRENT ROW). Do not use OVER () or a BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING frame: DuckDB crashes on those for every C API aggregate (FAQ). Without OVER, it returns the number of sessions in the group.

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;

Behavior

• The first event in each partition is assigned session ID 1.
• Each subsequent event is compared to the previous event's timestamp.
• If the gap exceeds the threshold, the session ID increments.
• A gap exactly equal to the threshold does not start a new session; the gap must strictly exceed the threshold.
• The threshold may differ per row: each row is compared with its own gap.
• Gaps touching DuckDB's ±infinity timestamps are computed exactly.
• With a descending ORDER BY, gaps are never positive and every row starts a new session; order ascending.

Example

Given events for a single user with a 30-minute threshold:

Errors

Invalid gap intervals abort the query with a descriptive SQL error instead of silently sessionizing with a zero threshold:

• Month-based gap — month intervals are ambiguous (28-31 days); use day/hour/minute/second units (e.g. INTERVAL '30 minutes')
• Negative gap — the gap must be non-negative

With a frame ending at the current row (the default), a NULL timestamp produces a NULL session ID for that row. The rule follows the frame's last row, so with a frame that ends elsewhere (... AND 1 FOLLOWING) it applies to that row instead. A row whose gap is NULL is left out of the session chain and receives the current session ID (NULL if no earlier row counted).

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

The state tracks the first timestamp, last timestamp, and the number of session boundaries (gaps exceeding the threshold). The combine operation is O(1), which enables efficient evaluation via DuckDB's segment tree windowing machinery.

At benchmark scale, sessionize processes 1 billion rows in 1.20 seconds (830 Melem/s).

See Also

• retention -- cohort retention analysis using boolean conditions
• window_funnel -- conversion funnel step tracking within time windows

retention

Aggregate function for cohort retention analysis. Takes N boolean conditions and returns an array indicating which conditions co-occurred with the anchor condition.

Signature

retention(cond1 BOOLEAN, cond2 BOOLEAN [, ...]) -> BOOLEAN[]

Parameters:

Supports 2 to 32 boolean parameters.

Returns: BOOLEAN[] -- an array of length N where element i indicates whether condition i was satisfied alongside the anchor condition.

Usage

SELECT cohort_month,
  retention(
    activity_date = cohort_month,
    activity_date = cohort_month + INTERVAL '1 month',
    activity_date = cohort_month + INTERVAL '2 months',
    activity_date = cohort_month + INTERVAL '3 months'
  ) as retained
FROM user_activity
GROUP BY user_id, cohort_month;

Behavior

• result[0] is true if the anchor condition (cond1) was satisfied by any row in the group.
• result[i] (for i > 0) is true if both the anchor condition and condition i were satisfied by some row(s) in the group. The conditions do not need to be satisfied by the same row.
• If the anchor condition was never satisfied, all results are false. A user who never appeared in the cohort cannot be retained.

Example

Given three rows for a user in a monthly cohort analysis:

Result: [true, true, false]

• The anchor was met (row 1).
• Month 1 retention is confirmed (row 2 satisfies cond2, anchor was met by row 1).
• Month 2 was never satisfied.

Implementation

Conditions are tracked as a u32 bitmask, where bit i is set when condition i evaluates to true for any row. The combine operation is a single bitwise OR.

At benchmark scale, retention combines 100 million states in 274 ms (365 Melem/s).

See Also

• sessionize -- session assignment based on timestamp gaps
• window_funnel -- conversion funnel step tracking within time windows

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

window_funnel_events

Aggregate function returning the timestamps of the best conversion funnel chain. Companion to window_funnel: where window_funnel tells you how far users got, window_funnel_events tells you when each step happened — invaluable for debugging funnels and computing step-to-step latencies.

Signature

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

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

Parameters:

Returns: TIMESTAMP[] (TIMESTAMPTZ[] for TIMESTAMPTZ input) -- one timestamp per matched funnel step, in match order. The list length always equals window_funnel's return value for the same arguments. Empty list when the entry condition never matched.

Usage

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

Step-to-Step Latency

WITH chains AS (
  SELECT user_id,
    window_funnel_events(INTERVAL '1 hour', event_time,
      event_type = 'page_view',
      event_type = 'add_to_cart',
      event_type = 'purchase'
    ) as chain
  FROM events
  GROUP BY user_id
)
SELECT user_id,
  chain[2] - chain[1] AS view_to_cart,
  chain[3] - chain[2] AS cart_to_purchase
FROM chains
WHERE len(chain) = 3;

Behavior

• Same scan as window_funnel (ClickHouse's windowFunnel algorithm), all modes supported.
• Returns the chain that reached the most steps. When the funnel completes, the scan stops at the first event that completes it; of the chains that event completes, the one with the latest entry is returned. When it does not complete, the chain with the latest entry among those reaching the most steps is returned (on a tie, the one completed last). For example, with c1 at 0 s and 2 s and c2 at 1 s and 3 s, a two-step funnel returns [0 s, 1 s]; adding a third step that never matches returns [2 s, 3 s].
• An event that fills several steps contributes its timestamp once per step, so the list length always equals the step count.

Errors

Shares window_funnel's validation: unknown mode strings, month-based or negative windows, and allow_reentry without strict_order abort the query with a descriptive SQL error. A row whose window is NULL is skipped; 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

Shares WindowFunnelState and the update/combine FFI callbacks with window_funnel — only finalize differs. The scan is generic over how a chain stores its step timestamps: window_funnel stores none, window_funnel_events stores one timestamp per step.

This function is an extension beyond ClickHouse: windowFunnel has no timestamp-returning companion there.

See Also

• window_funnel — step counts and mode semantics
• sequence_match_events — matched timestamps for sequence patterns

sequence_match

Aggregate function that checks whether a sequence of events matches a pattern. Uses a mini-regex syntax over condition references.

Signature

sequence_match(pattern VARCHAR, timestamp TIMESTAMP,
               cond1 BOOLEAN, cond2 BOOLEAN [, ...]) -> BOOLEAN

Parameters:

Returns: BOOLEAN -- true if the event stream contains a subsequence matching the pattern, false otherwise.

Usage

-- Did the user view a product and then purchase?
SELECT user_id,
  sequence_match('(?1).*(?2)', event_time,
    event_type = 'view',
    event_type = 'purchase'
  ) as converted
FROM events
GROUP BY user_id;

Pattern Syntax

Patterns are composed of the following elements:

Pattern Examples

-- View then purchase (any events in between)
'(?1).*(?2)'

-- View then purchase with no intervening events
'(?1)(?2)'

-- View, exactly one event, then purchase
'(?1).(?2)'

-- View, then purchase within 1 hour
'(?1).*(?t<=3600)(?2)'

-- Three-step sequence with time constraints
'(?1).*(?t<=3600)(?2).*(?t<=7200)(?3)'

Behavior

1. Events are sorted by timestamp.
2. The pattern is compiled into a list of steps.
3. Returns true if the pattern matches starting at any event. .* matches as few events as possible (lazy), which decides which match sequence_count and sequence_match_events report.

Time constraints are evaluated relative to the timestamp of the event consumed by the last (?N) or . step, in seconds. A constraint before any such step has nothing to measure from and is an error. ClickHouse instead measures a constraint that follows .* from the event after the last match, so (?1).*(?t<=3600)(?2) means "(?2) within an hour of (?1)" here but not there; see ClickHouse Compatibility.

Time-Constraint Semantics

(?t op N) mirrors ClickHouse (verified against its implementation): the constraint gates the next pattern step, and non-matching events in between are skipped while the gate can still hold — (?1)(?t<=10)(?2) matches (?2) at any event within 10 seconds of (?1). Trailing (?t<=N), (?t<N) and (?t>=0) match the empty remainder. N is interpreted in seconds with the elapsed time floored to whole seconds, so (?t==N) means "within [N, N+1) seconds" (the faithful generalization of ClickHouse's whole-second DateTime comparison to microsecond timestamps).

Determinism

Events sort by (timestamp, conditions) before matching, so results are deterministic regardless of thread count or physical row order. Events that satisfy no condition are dropped before matching (as in ClickHouse), so . and .* never consume them and they do not break adjacency.

Errors

A malformed pattern aborts the query with the parser's position-annotated message instead of silently returning NULL:

invalid sequence pattern '(?1)(?': pattern error at position 6: ...

So do a condition number above the number of conditions passed (condition (?3) is out of range; 2 conditions were passed) and a time constraint with no (?N) or . before it (time constraint must follow an event condition).

A pattern of more than 1024 steps is rejected (pattern has more than 1024 steps); consecutive .* count once.

A NULL pattern yields a NULL result (lenient), matching SQL aggregate conventions, as does a group with no rows or only rows whose timestamp is NULL.

The pattern argument 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 pattern 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

The last recorded benchmark (PERF.md Session 15) is 100 million events in 1.05 s for the fast-path pattern (?1).*(?2).*(?3).

Patterns outside the fast paths are matched in two passes: a backward pass marks, for each (?N) or . step, the events from which the rest of the pattern can still complete; a forward walk then takes the earliest such event at each step. This returns the same match as a lazy backtracking search would find first, without the search's quadratic worst case. Through v0.9.1 the extension used that search, and a group that did not match could take seconds (8.3–9.1 s for (?1).*(?t<5)(?2).*(?3) at 32,000 events). It now takes 0.004 s, and 0.78–0.89 s at 10 million events in one group (DuckDB 1.5.6, 3 runs each). Finalize uses about 8 bytes per event of working memory, plus one bit per event for each (?N) or . step (an 801-step pattern over 1 million events peaked at 148 MB, against 819 MB before the bit packing). Patterns are capped at 1024 steps, and an allocation that fails raises an out of memory error instead of crashing.

See Also

• sequence_count -- count non-overlapping matches of the same pattern
• sequence_match_events -- return the timestamps of each matched step
• sequence_next_node -- find the next event value after a pattern match

sequence_count

Aggregate function that counts the number of non-overlapping occurrences of a pattern in the event stream.

Signature

sequence_count(pattern VARCHAR, timestamp TIMESTAMP,
               cond1 BOOLEAN, cond2 BOOLEAN [, ...]) -> BIGINT

Parameters:

Returns: BIGINT -- the number of non-overlapping matches of the pattern in the event stream.

Usage

-- Count how many times a user viewed then purchased
SELECT user_id,
  sequence_count('(?1)(?2)', event_time,
    event_type = 'view',
    event_type = 'purchase'
  ) as conversion_count
FROM events
GROUP BY user_id;

Behavior

1. Events are sorted by timestamp.
2. The pattern is compiled and matched with the same engine as sequence_match.
3. Each time the pattern matches, the count increments and the search resumes where the match ended: after the last matched event or, when the pattern ends with a time constraint, at the event that satisfied it.
4. Matches are non-overlapping: once a set of events is consumed by a match, those events cannot participate in another match. A match that consumes no events (for example .*, or (?t<=6).*) still advances one event, as in ClickHouse, so sequence_count('.*', ...) counts one match per event.

Example

Given events for a user with pattern (?1)(?2):

Result: 2

• First match: events at 10:00 and 10:10.
• Second match: events at 10:20 and 10:30.
• The event at 10:40 has no subsequent cond2 event.

Pattern Syntax

Uses the same pattern syntax as sequence_match. Refer to that page for the full syntax reference.

Errors

A malformed pattern aborts the query with the parser's position-annotated message instead of silently returning NULL:

invalid sequence pattern '(?1)(?': pattern error at position 6: ...

An out-of-range condition number and a time constraint with no (?N) or . before it are errors too (see sequence_match).

A NULL pattern yields a NULL result (lenient), matching SQL aggregate conventions.

The pattern argument 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 pattern 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

At benchmark scale, sequence_count processes 100 million events in 1.18 s (85 Melem/s).

See Also

• sequence_match -- check whether the pattern matches (boolean)
• sequence_match_events -- return the timestamps of each matched step
• sequence_next_node -- find the next event value after a pattern match

sequence_match_events

Aggregate function that returns the timestamps of each matched condition step in a pattern. This is the diagnostic counterpart to sequence_match: instead of returning a boolean, it returns the actual timestamps where each (?N) step was satisfied.

Signature

sequence_match_events(pattern VARCHAR, timestamp TIMESTAMP,
                      cond1 BOOLEAN, cond2 BOOLEAN [, ...]) -> LIST(TIMESTAMP)

Parameters:

Returns: LIST(TIMESTAMP) (LIST(TIMESTAMPTZ) for TIMESTAMPTZ input) -- a list of timestamps corresponding to each matched (?N) step in the pattern. Returns an empty list if no match is found.

Usage

-- Get the timestamps where view and purchase occurred
SELECT user_id,
  sequence_match_events('(?1).*(?2)', event_time,
    event_type = 'view',
    event_type = 'purchase'
  ) as match_timestamps
FROM events
GROUP BY user_id;
-- Returns e.g. [2024-01-01 10:00:00, 2024-01-01 11:30:00]

Behavior

1. Events are sorted by (timestamp, conditions).
2. The pattern is compiled and matched with the same engine as sequence_match.
3. If the full pattern matches, the timestamps of the (?N) steps of the first match are returned: the match starting at the earliest event, with .* matching as few events as possible.
4. If the full pattern never matches, the timestamps of the longest partial chain are returned — the chain reaching the furthest (?N) step, the earliest-starting one if several do. This is ClickHouse's sequenceMatchEvents behavior ("timestamps for the longest chain of events matched the pattern"). The list is empty only when no (?N) step is ever reached.

The returned list contains one timestamp per matched (?N) step. Wildcard steps (. and .*) and time constraint steps do not contribute timestamps to the output.

Example

Given events for a user with pattern (?1).*(?2):

Result: [10:00, 10:30]

The timestamp of the (?1) match (10:00) and the (?2) match (10:30).

Pattern Syntax

Uses the same pattern syntax as sequence_match. Refer to that page for the full syntax reference.

Errors

A malformed pattern aborts the query with the parser's position-annotated message instead of silently returning NULL:

invalid sequence pattern '(?1)(?': pattern error at position 6: ...

An out-of-range condition number and a time constraint with no (?N) or . before it are errors too (see sequence_match).

A NULL pattern yields an empty list.

The pattern argument 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 pattern 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

For wildcard-separated patterns such as (?1).*(?2).*(?3), a single scan takes the earliest event for each condition in turn and stops at the first full match. Other patterns use the two-pass matcher described under sequence_match; when no full match exists, a forward pass first finds the furthest (?N) step any chain reaches.

The last recorded benchmark (PERF.md Session 15) is 100 million events in 1.07 s.

When a pattern matches, the timestamps are those of the events that matched. ClickHouse's sequenceMatchEvents can instead report an earlier, abandoned attempt of the same length; see ClickHouse Compatibility.

See Also

• sequence_match -- check whether the pattern matches (boolean)
• sequence_count -- count non-overlapping matches of the same pattern
• sequence_next_node -- find the next event value after a pattern match

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

Engineering Overview

This page provides a technical summary of the engineering decisions, quality standards, and design trade-offs that underpin duckdb-behavioral. It is intended for technical evaluators, contributors, and anyone seeking to understand the depth and rigor of this project.

Project Scope

duckdb-behavioral is a loadable extension for DuckDB that implements the complete set of behavioral analytics functions found in ClickHouse. The extension is written entirely in Rust, compiles to a shared library (.so / .dylib) that is stamped with DuckDB's metadata footer as a .duckdb_extension file, and integrates with DuckDB via the stable C extension API (C API v1.2.0, loadable into DuckDB 1.3.2 and later).

The project spans several distinct engineering disciplines:

Architecture

Separation of Concerns

The codebase enforces a strict separation between business logic and FFI integration:

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#ffffff', 'primaryTextColor': '#1a1a1a', 'primaryBorderColor': '#333333', 'lineColor': '#333333', 'secondaryColor': '#f5f5f5', 'tertiaryColor': '#e0e0e0', 'textColor': '#1a1a1a', 'mainBkg': '#ffffff', 'clusterBkg': '#f5f5f5', 'clusterBorder': '#333333'}}}%%
graph TB
    subgraph "DuckDB Process"
        DDB[DuckDB Engine]
        SEG[Segment Tree<br/>Windowing]
    end

    subgraph "Extension Entry Point"
        EP[entry_point_v2! macro<br/>src/lib.rs]
    end

    subgraph "FFI Bridge — src/ffi/"
        REG[register_all]
        FS[sessionize.rs]
        FR[retention.rs]
        FW[window_funnel.rs]
        FWE[window_funnel_events.rs]
        FQ[sequence.rs]
        FE[sequence_match_events.rs]
        FN[sequence_next_node.rs]
        FV[version.rs]
    end

    subgraph "Business Logic — Pure Safe Rust"
        SS[sessionize.rs]
        SR[retention.rs]
        SW[window_funnel.rs]
        SQ[sequence.rs]
        SN[sequence_next_node.rs]
        subgraph "Pattern Engine"
            PP[parser.rs]
            PE[executor.rs]
        end
        subgraph "Common"
            EV[event.rs — 16-byte Copy]
            TS[timestamp.rs]
        end
    end

    DDB -->|LOAD extension| EP
    EP --> REG
    REG --> FS & FR & FW & FWE & FQ & FE & FN & FV
    SEG -->|init/update/combine/finalize| FS & FR & FW & FWE & FQ & FE & FN
    FS --> SS
    FR --> SR
    FW --> SW
    FWE --> SW
    FQ --> SQ
    FE --> SQ
    FN --> SN
    SQ --> PP & PE
    SW --> EV
    SQ --> EV
    SN --> EV

    style EP fill:#d9d9d9,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style DDB fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SEG fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style REG fill:#f0f0f0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FS fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FR fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FW fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FWE fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FQ fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FE fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FN fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FV fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SS fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SR fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SW fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SQ fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SN fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style PP fill:#ffffff,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style PE fill:#ffffff,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style EV fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style TS fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a

• Business logic (src/*.rs, src/common/, src/pattern/): Pure safe Rust. No FFI types, no unsafe blocks. All algorithmic work -- pattern parsing, pattern matching, funnel search, retention bitmask logic -- lives here. Its unit tests exercise Rust structs directly, without a DuckDB connection.

• FFI bridge (src/ffi/, 9 files): Contains all unsafe code. Each aggregate has an FFI module wiring the DuckDB aggregate callbacks (state_size, init, update, combine, finalize, destroy); version.rs registers the behavioral_version() scalar. The update, combine, and finalize callbacks are generated with quack-rs's aggregate_{update,combine,finalize}_callback! macros, which catch a panic and report it as a SQL error (the release profile uses panic = "unwind" so this guard works). Every callback's unsafe block has a // SAFETY: comment documenting the invariants it relies on.

• Entry point (src/lib.rs): Uses the quack_rs::entry_point_v2! macro with the Connection/Registrar trait pattern. Functions are registered via con.register_aggregate_set(builder) (variadic aggregates) and con.register_aggregate(builder) (sessionize). This replaces ~80 lines of hand-rolled unsafe code and eliminates struct layout fragility across DuckDB versions.

Why This Matters

This architecture enables:

• Independent unit testing: The 547 unit tests exercise Rust structs directly and run in about two seconds, without loading the extension.
• Safe evolution: Updating the DuckDB version only requires updating libduckdb-sys in Cargo.toml and re-running E2E tests. Business logic is decoupled from the database.
• Auditable unsafe scope: The unsafe boundary is confined to src/ffi/ (9 files: mod.rs, 7 aggregate modules, version.rs). Reviewers can audit the safety-critical code without reading the entire codebase.

Testing Philosophy

The Test Pyramid

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#ffffff', 'primaryTextColor': '#1a1a1a', 'primaryBorderColor': '#333333', 'lineColor': '#333333', 'secondaryColor': '#f5f5f5', 'tertiaryColor': '#e0e0e0', 'textColor': '#1a1a1a', 'clusterBkg': '#f5f5f5', 'clusterBorder': '#333333'}}}%%
graph TB
    subgraph "Complementary Test Levels"
        L3["Mutation Testing<br/>93.9% detected (570/607, 0.10.0 audit)<br/>cargo-mutants"]
        L2["Integration + E2E<br/>28 in-process LOAD tests<br/>8 sqllogictest files on the DuckDB CLI"]
        L1["Unit Tests (547)<br/>State lifecycle, edge cases, combine correctness<br/>Property-based (33 proptest), mutation-guided (67)"]
    end

    style L1 fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style L2 fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style L3 fill:#d9d9d9,stroke:#333333,stroke-width:2px,color:#1a1a1a

This project implements a rigorous multi-level testing strategy:

Level 1: Unit Tests (547 tests)

Organized by category within each module:

• State lifecycle tests -- empty state, single update, multiple updates, finalize
• Edge cases -- threshold boundaries, NULL handling, empty inputs, overflow at type boundaries (u32::MAX, i64::MIN)
• Combine correctness -- empty-into-empty, empty-into-populated, populated-into-empty, associativity verification, configuration propagation
• Property-based tests (33 proptest) -- algebraic properties required by DuckDB's segment tree: combine associativity, commutativity, identity element, idempotency, monotonicity; plus two differential tests that check the sequence matcher against the original backtracking search on random patterns and event sets
• Mutation-testing-guided tests (67) -- tests written specifically to kill mutants that survived earlier test suites (51 from the v0.4.x run, 16 from the 0.10.0 run)

Level 2: E2E Tests (8 sqllogictest files: 44 query + 34 statement directives)

SQL logic tests in test/sql/*.test, run against a real DuckDB CLI instance, that validate the complete chain: extension loading, function registration, SQL execution, and result correctness. These tests caught three critical bugs that the entire unit suite missed:

1. A segmentation fault on extension load (incorrect pointer arithmetic)
2. Six of the seven functions that existed then silently failing to register (missing API call)
3. window_funnel returning incorrect results (combine not propagating configuration)

This same load → register → execute chain also runs in-process inside cargo test (tests/extension_load.rs, 28 tests; run with DUCKDB_DOWNLOAD_LIB=1 cargo test). It builds the real release cdylib, appends the DuckDB metadata footer, and LOADs it via quack_rs::testing::InMemoryDb::open_unsigned() — so the entire class of FFI-registration bugs above is now caught by the regular test suite, not only by the CLI-based E2E job in CI.

Level 3: Mutation Testing (93.9% detected, 0.10.0 audit)

cargo-mutants systematically replaces operators, removes branches, and changes return values across the codebase. The 0.10.0 audit of the 9 non-FFI modules detected 570 of 607 viable mutants (547 caught, 23 timed out); v0.4.x had caught 130 of 147. Tests were then written for the surviving mutants that change behaviour, and window_funnel.rs, sequence_next_node.rs and pattern/parser.rs were re-run: 320 of 342 viable detected. The 22 remaining survivors were reviewed by hand as equivalent (e.g. OR vs XOR on non-overlapping bit fields, the hash function of a cache, a function never called for one path type).

Key Insight

Unit tests validate algorithmic correctness in isolation. E2E tests validate the integration boundary. Neither alone is sufficient. The three bugs found during E2E validation were entirely in the FFI layer -- a domain that unit tests by definition cannot exercise.

Performance Engineering

Methodology

Every performance claim in this project is a Criterion microbenchmark of the Rust aggregate state (not an end-to-end SQL query), backed by:

• Criterion.rs benchmarks with 95% confidence intervals
• Multiple runs (minimum 3) to establish baselines
• Throughput reporting (elements per second) alongside wall clock time
• Negative results documented honestly -- five attempted optimizations were measured, found to be regressions, reverted, and documented

Scale

Optimization History

Optimization sessions recorded in PERF.md (Sessions 1 through 18, with before/after measurements), each following a documented protocol:

1. Establish baseline with 3 Criterion runs
2. Implement one optimization per commit
3. Measure before/after with confidence intervals
4. Accept only when confidence intervals do not overlap
5. Revert and document if the improvement is not statistically significant

Selected highlights:

Five attempted optimizations were negative results:

• LSD radix sort: 4.3x slower than pdqsort for 16-byte embedded-key structs
• Branchless sessionize: 5--10% regression; branch predictor handles 90/10 patterns efficiently
• String pool with index vectors: 10--55% slower than Arc<str> at most scales
• Compiled pattern preservation: Caching parsed patterns did not improve performance
• First-condition pre-check: Adding an early-exit branch for non-matching first conditions increased overhead

Full optimization history with confidence intervals: Performance

Domain Significance

Why Behavioral Analytics Matters

Behavioral analytics answers a class of questions that traditional SQL aggregations cannot express efficiently:

• Sessionization: Given a stream of timestamped events, partition them into logical sessions based on inactivity gaps. This is fundamental to web analytics, mobile app analysis, and IoT telemetry.

• Retention cohorts: Given users who first appeared at time T, what fraction returned at T+1, T+2, T+N? This is the core metric for SaaS businesses, subscription services, and any product measuring long-term engagement.

• Conversion funnels: Given a multi-step process (browse, add to cart, checkout, purchase), how far does each user progress within a time window? This directly drives revenue optimization in e-commerce.

• Event sequence patterns: Given a temporal sequence of heterogeneous events, does a specific pattern occur? How many times? What happened next? This generalizes to fraud detection, clinical event monitoring, user journey analysis, and process mining.

Who Needs This

Scale Context

These problems are inherently large-scale. A mid-sized SaaS application generates millions of events per day. An e-commerce platform during a sale event can produce hundreds of millions of events per hour. The ability to update a sessionize state with one billion events in 1.20 seconds on a single core (a Criterion microbenchmark of the Rust state, not a SQL query) suggests these analyses can run as interactive queries rather than batch jobs.

Implementation Challenges

DuckDB Aggregate Function Registration

DuckDB's Rust crate does not provide high-level aggregate function registration. This project uses the quack-rs SDK (v0.19.0) which wraps the raw C API with safe builders (including returns_logical(LogicalType) for LIST(T) return types), state management, vector I/O, LIST output helpers, and the aggregate_{update,combine,finalize}_callback! macros that turn a panic into a SQL error. All 8 aggregate functions use quack-rs builders for registration: the seven variadic functions via AggregateFunctionSetBuilder, and sessionize via AggregateFunctionBuilder (a single fixed signature — DuckDB windows any aggregate, so no window-specific registration exists). Each aggregate implements five callback functions:

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#f5f5f5', 'primaryTextColor': '#1a1a1a', 'primaryBorderColor': '#333333', 'lineColor': '#333333', 'secondaryColor': '#e8e8e8', 'tertiaryColor': '#e0e0e0', 'textColor': '#1a1a1a', 'noteBkgColor': '#e8e8e8', 'noteTextColor': '#1a1a1a', 'noteBorderColor': '#333333', 'labelTextColor': '#1a1a1a', 'transitionColor': '#333333'}}}%%
stateDiagram-v2
    [*] --> state_size: DuckDB queries state byte size

    state_size --> init: Allocate state buffer
    init --> update: Process input rows

    state "update" as update {
        [*] --> ProcessRow
        ProcessRow --> AccumulateState
        AccumulateState --> ProcessRow: Next row
    }

    update --> combine: Segment tree merge
    note right of combine
        Called O(n log n) times.
        Target state is zero-initialized.
        Must propagate ALL config fields.
    end note

    combine --> finalize: Produce result
    finalize --> destroy: Free resources
    destroy --> [*]

state_size  -- Returns the byte size of the aggregate state
init        -- Initializes a new state (called by DuckDB's segment tree)
update      -- Processes one row of input into the state
combine     -- Merges two states (called O(n log n) times by segment tree)
finalize    -- Produces the final result from the accumulated state

Because duckdb_aggregate_function_set_varargs does not exist, each function that accepts a variable number of boolean conditions registers one overload per arity via a function set:

sessionize and the behavioral_version() scalar have a single signature.

Pattern Engine

The sequence functions use a custom pattern engine:

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#ffffff', 'primaryTextColor': '#1a1a1a', 'primaryBorderColor': '#333333', 'lineColor': '#333333', 'secondaryColor': '#f5f5f5', 'tertiaryColor': '#e0e0e0', 'textColor': '#1a1a1a'}}}%%
flowchart LR
    SQL["Pattern String<br/>'(?1).*(?t<=3600)(?2)'"] --> PARSE["Recursive Descent<br/>Parser"]
    PARSE --> STEPS["CompiledPattern<br/>Condition(1), AnyEvents,<br/>TimeConstraint(<=, 3600),<br/>Condition(2)"]
    STEPS --> CLASS{Classify}
    CLASS -->|"All adjacent"| FAST1["O(n) Sliding<br/>Window"]
    CLASS -->|"Wildcard-separated"| FAST2["O(n) Linear<br/>Scan"]
    CLASS -->|"Time constraints<br/>or mixed"| NFA["Feasibility pass +<br/>greedy walk"]

    style SQL fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style PARSE fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style STEPS fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style CLASS fill:#ffffff,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FAST1 fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style FAST2 fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style NFA fill:#d9d9d9,stroke:#333333,stroke-width:2px,color:#1a1a1a

• A recursive descent parser that compiles pattern strings (e.g., (?1).*(?t<=3600)(?2)) into an intermediate representation of typed steps
• A general matcher for every other shape: one backward pass per event-consuming step marks the positions from which the rest of the pattern can still complete, then a forward walk takes the earliest such position at each step. It returns exactly the match a lazy (.* matches as little as possible) backtracking search finds first, in O(s · n log n) for n events and s pattern steps. The extension used that backtracking search through v0.9.1; it was replaced for being quadratic per group on patterns with .* or skipping time constraints. It is kept as a test-only oracle, and differential property tests check that the two agree.
• Fast-path classifiers that detect common pattern shapes (adjacent conditions, wildcard-separated conditions) and dispatch to specialized O(n) linear scans

Combine Semantics in DuckDB's Segment Tree

DuckDB's windowing and grouping machinery uses a segment tree that creates fresh zero-initialized states and combines source states into them. This creates a subtle correctness requirement: combine must propagate all configuration fields (window size, mode flags, pattern string, direction/base parameters), not just the accumulated data. Missing this caused silent incorrect results that passed all unit tests but failed E2E validation.

Quality Standards

Technology Stack

Architecture

This page describes the internal architecture of the duckdb-behavioral extension, including the module structure, design decisions, and the FFI bridge between Rust business logic and DuckDB's C API.

Module Structure

src/
  lib.rs                       Entry point via quack_rs::entry_point_v2! macro
  common/
    mod.rs
    event.rs                   Event type (u32 bitmask, Copy, 16 bytes)
    timestamp.rs               Interval-to-microseconds conversion
  pattern/
    mod.rs
    parser.rs                  Recursive descent parser for pattern strings
    executor.rs                Pattern matcher: fast paths + feasibility/greedy matcher
    reference_nfa.rs           Test-only: original backtracking search (differential oracle)
  sessionize.rs                Session boundary tracking (O(1) combine)
  retention.rs                 Bitmask-based cohort retention (O(1) combine)
  window_funnel.rs             Port of ClickHouse windowFunnel, combinable mode bitflags
  sequence.rs                  Sequence match/count/events state management
  sequence_next_node.rs        Next event value after pattern match (Arc<str>)
  ffi/
    mod.rs                     register_all() dispatcher
    sessionize.rs              FFI via quack-rs AggregateFunctionBuilder (fixed signature)
    retention.rs               FFI via quack-rs builder + returns_logical(LIST(BOOLEAN)) + ListVector
    window_funnel.rs           FFI via quack-rs builder + FfiState + VectorReader/VectorWriter
    window_funnel_events.rs    FFI sharing window_funnel update/combine + LIST(TIMESTAMP) finalize
    sequence.rs                FFI via quack-rs builder for sequence_match/count
    sequence_match_events.rs   FFI via quack-rs builder + returns_logical(LIST(TIMESTAMP)) + ListVector
    sequence_next_node.rs      FFI via quack-rs builder + VectorWriter::write_varchar
    version.rs                 behavioral_version() scalar via quack-rs ScalarFunctionBuilder

Design Principles

Pure Rust Core with FFI Bridge

All business logic lives in the top-level modules (sessionize.rs, retention.rs, window_funnel.rs, sequence.rs, sequence_next_node.rs, pattern/) with zero FFI dependencies. These modules are fully testable in isolation using standard Rust unit tests.

The ffi/ submodules handle DuckDB C API registration exclusively. This separation ensures that:

• Business logic can be tested without a DuckDB instance.
• The unsafe FFI boundary is confined to a single directory.
• Every callback's unsafe block has a // SAFETY: comment documenting its invariants.
• The update, combine, and finalize callbacks are generated by quack-rs's aggregate_{update,combine,finalize}_callback! macros, which turn a panic into a SQL error. The release profile sets panic = "unwind" so that this guard is effective.

Aggregate Function Lifecycle

Each aggregate registers these callbacks (plus a destroy callback that frees the Rust state):

Each FFI module uses quack_rs::aggregate::FfiState<T> for safe state management. FfiState<T> handles heap allocation, null-checked access via with_state_mut(), and memory reclamation — replacing hand-rolled Box::from_raw patterns.

Function Sets for Variadic Signatures

DuckDB does not provide a duckdb_aggregate_function_set_varargs API. To support variable numbers of boolean condition parameters, each variadic function is registered as a function set with one overload per arity:

The sessionize function has a fixed two-parameter signature and does not require a function set; neither does the behavioral_version() scalar.

Entry Point via quack-rs

The extension uses the quack_rs::entry_point_v2! macro with the Connection/Registrar trait pattern. Functions are registered via con.register_aggregate_set(builder) (variadic aggregates), con.register_aggregate(builder) (sessionize), and con.register_scalar(builder) (behavioral_version). This replaces ~80 lines of hand-rolled unsafe code from earlier versions.

quack-rs SDK

The FFI layer uses quack-rs v0.19.0, a Rust SDK for DuckDB loadable extensions. It provides:

• AggregateFunctionSetBuilder: Safe registration of function sets with automatic per-overload naming. Supports both .returns(TypeId) for simple types and .returns_logical(LogicalType) for parameterized types like LIST(T).
• FfiState<T>: Null-checked state management with with_state_mut() returning Option<&mut T>, safe init/destroy lifecycle.
• VectorReader: Safe input reading (read_bool(), read_str(), read_i64(), read_interval(), is_valid()).
• VectorWriter: Safe output writing (write_i32(), write_bool(), write_varchar(), set_null() with automatic validity bitmap setup).
• ListVector: Safe LIST output (reserve(), set_size(), set_entry(), child_writer()) replacing raw duckdb_list_vector_* pointer arithmetic.
• LogicalType::list(): RAII construction of LIST(T) types for returns_logical().
• aggregate_{update,combine,finalize}_callback!: Macros that generate the extern "C" callbacks with a panic guard, reporting a panic as a SQL error.
• AggregateTestHarness: Unit-test harness for verifying combine config-propagation without E2E testing.
• testing::InMemoryDb (dev-only bundled-test-prebuilt feature): used by tests/extension_load.rs to LOAD the built extension in-process.

All 8 aggregate functions use quack-rs builders for registration: the seven variadic functions via AggregateFunctionSetBuilder (including retention, window_funnel_events, and sequence_match_events, which use .returns_logical(LogicalType::list(...)) for their LIST(T) return types), and sessionize via AggregateFunctionBuilder — DuckDB windows any aggregate, so no window-specific registration exists. The duckdb crate is declared as a dev-dependency (without the bundled feature) so that test builds link a prebuilt libduckdb (DUCKDB_DOWNLOAD_LIB=1 or DUCKDB_LIB_DIR); no code in src/, tests/, or benches/ references it.

The extension uses only the stable C API. The .duckdb_extension file is stamped with C API version v1.2.0 and ABI type C_STRUCT, so one binary loads into DuckDB 1.3.2 and later (CI checks 1.3.2, 1.4.4, 1.5.0, 1.5.6, and 2.0.0).

Event Representation

The Event struct is shared across window_funnel, sequence_match, sequence_count, and sequence_match_events:

#![allow(unused)]
fn main() {
#[derive(Debug, Clone, Copy, PartialEq, Eq)]
pub struct Event {
    pub timestamp_us: i64,   // 8 bytes
    pub conditions: u32,     // 4 bytes
    // 4 bytes padding (alignment to 8)
}                            // Total: 16 bytes
}

Design choices:

• u32 bitmask instead of Vec<bool> eliminates per-event heap allocation and enables O(1) condition checks via bitwise AND. Supports up to 32 conditions (ClickHouse's windowFunnel and sequenceMatch also allow 32; its sequenceNextNode allows 64).
• Copy semantics enable zero-cost event duplication in combine operations.
• 16 bytes packs four events per 64-byte cache line, maximizing spatial locality during sorting and scanning.
• Expanding from u8 (8 conditions) to u32 (32 conditions) was a zero-cost change: the struct is 16 bytes in both cases due to alignment padding from the i64 field.

Pattern Engine

The pattern engine (src/pattern/) compiles pattern strings into a structured list of steps and matches them against the sorted events.

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#ffffff', 'primaryTextColor': '#1a1a1a', 'primaryBorderColor': '#333333', 'lineColor': '#333333', 'secondaryColor': '#f5f5f5', 'tertiaryColor': '#e0e0e0', 'textColor': '#1a1a1a'}}}%%
flowchart LR
    SQL["SQL Pattern String<br/>'(?1).*(?t<=3600)(?2)'"]
    PARSE["Recursive Descent<br/>Parser"]
    CP["CompiledPattern<br/>Vec of PatternStep"]
    CLASS["Pattern<br/>Classifier"]

    SQL --> PARSE --> CP --> CLASS

    CLASS -->|Adjacent| ADJ["O(n) Sliding Window"]
    CLASS -->|Wildcard-separated| WC["O(n) Linear Scan"]
    CLASS -->|Complex| NFA["Feasibility pass +<br/>greedy walk"]

    ADJ --> RES["MatchResult"]
    WC --> RES
    NFA --> RES

    style SQL fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style PARSE fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style CP fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style CLASS fill:#ffffff,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style ADJ fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style WC fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style NFA fill:#d9d9d9,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style RES fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a

Parser

The recursive descent parser (parser.rs) converts pattern strings like (?1).*(?t<=3600)(?2) into a CompiledPattern containing a sequence of PatternStep variants:

• Condition(usize) -- match an event where condition N is true
• AnyEvents -- match zero or more events (.*)
• OneEvent -- match exactly one event (.)
• TimeConstraint(TimeOp, i64) -- time constraint between steps

Executor

The executor (executor.rs) classifies patterns at execution time and dispatches to specialized code paths:

• Adjacent conditions ((?1)(?2)(?3)): O(n) sliding window scan
• Wildcard-separated ((?1).*(?2).*(?3)): O(n) single-pass linear scan
• Everything else: a backward pass per (?N)/. step marks the events from which the rest of the pattern can still complete, then a forward walk takes the earliest such event at each step. O(s · n log n) for s pattern steps.

Two entry points are provided:

Results are defined by a lazy backtracking search (.* matches as few events as possible; the first match from the earliest start wins), which the extension used through v0.9.1. That search is quadratic per group when the pattern does not complete, so it survives only as a test oracle (reference_nfa.rs); differential property tests check the executor against it. The greedy walk reproduces its first match because each gap step (.* or a time constraint, measured from the same event) reaches a superset of positions when it starts earlier.

Combine Strategy

DuckDB's segment tree windowing calls combine O(n log n) times. The combine implementation is the dominant cost for event-collecting functions.

%%{init: {'theme': 'base', 'themeVariables': {'primaryColor': '#ffffff', 'primaryTextColor': '#1a1a1a', 'primaryBorderColor': '#333333', 'lineColor': '#333333', 'secondaryColor': '#f5f5f5', 'tertiaryColor': '#e0e0e0', 'textColor': '#1a1a1a', 'clusterBkg': '#f5f5f5', 'clusterBorder': '#333333'}}}%%
flowchart TB
    subgraph "DuckDB Segment Tree"
        R["Root State"]
        L1["State A"]
        L2["State B"]
        L3["State C"]
        L4["State D"]
        R --- L1 & L2
        L1 --- L3 & L4
    end

    subgraph "Combine Operation"
        T["Target<br/>(zero-initialized)"]
        S["Source State"]
        T -->|"extend events<br/>propagate config"| M["Merged State"]
        S -->|"append events"| M
    end

    subgraph "Finalize"
        M -->|"sort events"| SORT["Sorted Events"]
        SORT -->|"scan / match"| OUT["Result"]
    end

    style R fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style L1 fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style L2 fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style L3 fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style L4 fill:#e8e8e8,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style T fill:#f0f0f0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style S fill:#f0f0f0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style M fill:#d9d9d9,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style SORT fill:#e0e0e0,stroke:#333333,stroke-width:2px,color:#1a1a1a
    style OUT fill:#f5f5f5,stroke:#333333,stroke-width:2px,color:#1a1a1a

The in-place combine (combine_in_place) extends self.events directly instead of allocating a new Vec. This reduces a left-fold chain from O(N^2) total copies to O(N) amortized. Events are sorted once during finalize, not during combine. This deferred sorting strategy avoids redundant work.

Sorting

Events are sorted with an unstable sort (pdqsort) on a total key:

1. An O(n) presorted check (windows(2) over the same key) detects input that is already in key order and skips the sort entirely.
2. For unsorted input, pdqsort provides O(n log n) worst-case with good cache locality for 16-byte Copy types.

Ties on timestamp are broken by the remaining key fields, so the result is deterministic regardless of the order in which DuckDB delivers rows or combines partial states. A stable sort would not give that guarantee, because arrival order itself varies with parallel aggregation.

Performance

All performance claims in this project are backed by measured data from Criterion.rs benchmarks with 95% confidence intervals, validated across three or more consecutive runs. Improvements are accepted only when confidence intervals do not overlap.

The benchmarks are microbenchmarks of the Rust aggregate state (update, combine, finalize), not end-to-end SQL queries through DuckDB.

Current Baseline

Measured on the development machine. Absolute numbers vary by hardware; relative improvements and algorithmic scaling are hardware-independent. These are the PERF.md Session 15 headline numbers, recorded before v0.8.0 and not re-measured since (PERF.md Session 18 measured the cost of the v0.8.0 correctness changes on selected benchmarks).

Headline Numbers

Per-Element Cost

Algorithmic Complexity

Where n = events, m = events in other state, k = conditions (up to 32), s = pattern steps.

Optimization History

PERF.md records the optimization sessions (numbered from Session 1 to Session 18) that produced the current performance profile. Each optimization below was measured independently with before/after Criterion data and non-overlapping confidence intervals (except where noted as negative results).

Event Bitmask Optimization

Replaced Vec<bool> per event with a u32 bitmask, eliminating per-event heap allocation and enabling Copy semantics. The Event struct shrank from 32+ bytes with heap allocation to 16 bytes with zero heap allocation.

In-Place Combine

Replaced O(N^2) merge-allocate combine with O(N) in-place extend. Switched from stable sort (TimSort) to unstable sort (pdqsort). Eliminated redundant pattern cloning.

NFA Lazy Matching

Fixed .* exploration order in the NFA executor. The NFA was using greedy matching (consume event first), causing O(n^2) backtracking. Switching to lazy matching (advance pattern first) reduced complexity to O(n * pattern_len).

Billion-Row Benchmarks

Expanded benchmarks to 100M for event-collecting functions and 1B for O(1)-state functions (PERF.md Session 4; the retention_combine benchmark has since been capped at 100M, so only sessionize_update still runs at 1B). Validated that all functions scale as documented with no hidden bottlenecks at extreme scale.

ClickHouse Feature Parity

Architectural refactoring session. Implemented combinable FunnelMode bitflags, three new window_funnel modes (strict_increase, strict_once, allow_reentry), multi-step advancement per event, and the sequence_match_events function. No performance optimization targets.

Presorted Detection + 32-Condition Support

Added an O(n) is_sorted check before sorting. When events already arrive in timestamp order, the O(n log n) sort is skipped entirely. The O(n) overhead for unsorted input is negligible.

Also expanded condition support from u8 (8 conditions) to u32 (32 conditions). This was a zero-cost change: the Event struct remains 16 bytes due to alignment padding from the i64 field.

Negative Results

Session 7 measured two optimization hypotheses that produced negative results and ruled out a third without implementing it. These are documented to prevent redundant investigation:

• LSD Radix Sort: 8-bit radix, 8 passes. Measured 4.3x slower at 100M elements. The scatter pattern has poor cache locality for 16-byte structs. pdqsort's in-place partitioning is superior for embedded-key structs.

• Branchless Sessionize: Replaced conditional branches with i64::from(bool) and max/min. Measured 5-10% slower. The branch predictor handles the 90/10 gap-exceeds-threshold pattern with near-perfect accuracy. CMOV adds fixed overhead that exceeds the near-zero cost of correctly predicted branches.

• Retention Update Micro-optimization: Analysis showed the benchmark bottleneck is Vec<bool> allocation in the test harness, not the bitmask update loop. No optimization possible without changing the benchmark structure.

sequence_next_node Implementation

Implemented sequence_next_node with full direction (forward/backward) and base (head/tail/first_match/last_match) support. Uses a dedicated NextNodeEvent struct with per-event string storage (separate from the Copy Event struct). Simple sequential matching algorithm.

Added a dedicated benchmark (benches/sequence_next_node_bench.rs) measuring update + finalize throughput and combine operations at multiple scales. No performance optimization targets — this session focused on feature completeness, achieving complete ClickHouse behavioral analytics parity.

Arc<str> Optimization

Replaced Option<String> with Option<Arc<str>> in NextNodeEvent, reducing struct size from 40 to 32 bytes and enabling O(1) clone via reference counting.

A string pool attempt (PooledEvent with Copy semantics, 24 bytes) was measured 10-55% slower at most scales due to dual-vector overhead. The key insight: below one cache line (64 bytes), clone cost matters more than absolute struct size. Arc::clone (~1ns atomic increment) consistently beats String::clone (copies len bytes).

Also added a realistic cardinality benchmark using a pool of 100 distinct Arc<str> values, showing 35% improvement over unique strings at 1M events.

E2E Validation + Custom C Entry Point

Discovered and fixed 3 critical bugs that all 375 passing unit tests at the time (now 547) missed:

1. SEGFAULT on extension load: extract_raw_connection used incorrect pointer arithmetic. Replaced with a custom C entry point (behavioral_init_c_api) that obtains duckdb_connection via duckdb_connect directly from duckdb_extension_access.

2. Silent function registration failure: duckdb_aggregate_function_set_name must be called on each function in a set. Without this, 6 of 7 functions silently failed to register.

3. Incorrect combine propagation: combine_in_place did not propagate window_size_us or mode. DuckDB's segment tree creates fresh zero-initialized target states, so these fields remained zero at finalize time.

No performance optimization targets. 11 E2E tests added against real DuckDB v1.4.4 CLI.

NFA Fast Paths

Two Criterion-validated optimizations for the NFA pattern executor:

1. NFA reusable stack: Pre-allocate the NFA state Vec once and reuse across all starting positions via clear(). Eliminates O(N) alloc/free pairs in sequence_count.

2. Fast-path linear scan: Pattern classification dispatches common shapes to specialized O(n) scans:

   • Adjacent conditions ((?1)(?2)): sliding window scan
   • Wildcard-separated ((?1).*(?2)): single-pass step counter
   • Complex patterns: fall back to NFA

A first-condition pre-check was tested and produced negative results (0-8% regression). The NFA already handles non-matching starts efficiently (~5ns per check), so the extra branch in the hot loop costs more than it saves. Reverted.

Benchmark Inventory

Reproduction

# Run all benchmarks
cargo bench

# Run a specific group
cargo bench -- sessionize
cargo bench -- window_funnel
cargo bench -- sequence_match_events

# Results stored in target/criterion/ for comparison

Criterion automatically compares against previous runs and reports confidence intervals. Run benchmarks three or more times before drawing conclusions from the data.

Methodology

• Framework: Criterion.rs 0.8, 100 samples per benchmark, 95% CI
• Large-scale benchmarks: reduced sample counts and longer measurement times for 100M and 1B element counts
• Validation: Every claim requires three consecutive runs with non-overlapping confidence intervals
• Negative results: Documented with the same rigor as positive results, including measured data and root cause analysis

Full optimization history with raw Criterion data, confidence intervals, and detailed analysis is maintained in PERF.md in the repository.

ClickHouse Compatibility

duckdb-behavioral implements behavioral analytics functions inspired by ClickHouse's parametric aggregate functions. This page documents the compatibility status, semantic differences, and remaining gaps.

Compatibility Matrix

Behavioral Parametric Functions

All six ClickHouse behavioral parametric functions are implemented. "Verified" means differential testing against ClickHouse 26.9.8.3 found no difference outside the deliberate ones listed under Known Semantic Differences.

Non-Behavioral Parametric Functions (Out of Scope)

ClickHouse's parametric aggregate functions page documents 10 functions total. Six are behavioral analytics functions (all implemented above). The remaining four are general-purpose aggregate functions that share the parametric calling convention but operate on fundamentally different problem domains. They are not behavioral analytics functions and are therefore not in scope for this extension.

The distinction is precise: behavioral analytics functions analyze sequences of user actions over time — they require a timestamp, operate on ordered event streams, and answer questions about user journeys, funnels, retention, and patterns. The four excluded functions do not operate on event sequences, do not require timestamps, and do not model user behavior.

These four functions happen to share ClickHouse's parametric calling convention function(params)(args) with the behavioral functions, which is why they appear on the same documentation page. However, the parametric convention is a syntax pattern, not a semantic category. DuckDB does not use this convention at all (all parameters are flat), making the grouping irrelevant to our extension's scope.

Syntax Differences

ClickHouse uses a two-level call syntax for parametric aggregate functions:

-- ClickHouse syntax
windowFunnel(3600)(timestamp, cond1, cond2, cond3)

-- duckdb-behavioral syntax
window_funnel(INTERVAL '1 hour', timestamp, cond1, cond2, cond3)

Key differences:

The sessionize function has no direct ClickHouse equivalent. ClickHouse provides session analysis through different mechanisms.

How Parity Was Verified

Each function was run in both engines on the same randomly generated event groups (whole-second timestamps, frequent ties, events matching zero, one or several conditions, every mode combination) and the results compared group by group. ClickHouse ran as clickhouse local 26.9.8.3; every difference was minimized and explained from ClickHouse's source at tag v26.9.8.3-stable.

Semantic Compatibility

retention

Compatible. result[0] reflects the anchor condition; result[i] requires both the anchor and condition i to have been true in the group.

window_funnel

window_funnel runs ClickHouse's windowFunnel scan (AggregateFunctionWindowFunnel.cpp): each event contributes one entry per condition it satisfies (so one event can fill several steps, the entry step included), entries are visited in (timestamp, condition) order, and per funnel level the chain with the latest entry is kept. The modes follow ClickHouse:

• strict_deduplication (also 'strict', which ClickHouse 26.9 rejects): a condition firing again for a step already reached stops the scan.
• strict_order: once a chain has been entered, an event matching no condition, or a step arriving before its predecessor, stops the scan.
• strict_increase: each step must be strictly later than the one before.
• strict_once: an event fills at most one step of a chain.
• allow_reentry: requires strict_order (an error otherwise, as in ClickHouse); a step arriving before its predecessor is skipped instead of stopping the scan.
• timestamp_dedup (extension): same as strict_increase.

sequence_match, sequence_count, sequence_match_events

Pattern syntax matches ClickHouse: (?N) conditions (1-indexed, at most the number of conditions passed), ., .*, and (?t op N) with op one of >=, <=, >, <, ==, plus the extension operator !=. A time constraint gates the next step without consuming events, and non-matching events between gated steps are skipped, as in ClickHouse. sequence_count counts non-overlapping matches; a match that consumes no events (for example .*) advances one event, as in ClickHouse.

sequence_next_node

Matches ClickHouse's sequenceNextNode: a single anchor per base (head/tail = the literal first/last event, which must satisfy base_condition; first_match/last_match = the first/last event satisfying base_condition and event1), the chain must match consecutive events, and a failed chain is not retried at another anchor. forward with tail and backward with head are rejected, as in ClickHouse.

Extensions Beyond ClickHouse

Known Semantic Differences

Deliberate, because ClickHouse's behaviour is defective or depends on row order:

1. windowFunnel with strict_increase (without strict_once) loses chains in ClickHouse. It keeps one chain per level, and a later entry overwrites a valid earlier chain that it then cannot use under the strict comparison: events c1@0, c1@1, c2@1 give 1, although c1@0 -> c2@1 is a valid chain. ClickHouse's own strict_once path, which keeps every chain, gives 2. The extension keeps the best chain ending before each timestamp and gives 2. Combined with strict_deduplication, the kept chain can make a later repeat count, so the extension's answer can also be lower.

2. Ties that ClickHouse leaves in arrival order. windowFunnel with strict_once orders same-timestamp rows by arrival, and with strict_deduplication its answer then depends on row order (rows c2@2, c1&c2&c3@2, c3@3 give 2 or 3). sequenceNextNode keeps events tying on (timestamp, value) in arrival order. The extension orders ties by a total key (the condition bitmask; for sequence_next_node, (timestamp, value, base_condition, conditions)), so results never depend on row order or thread count. Every differing windowFunnel case checked (547) is an answer ClickHouse itself returns for some ordering of the rows.

3. sequenceMatch and friends also order same-timestamp events by arrival (::sort on timestamp only); the extension orders them by condition bitmask.

4. Time-constraint anchor. The extension measures (?t op N) from the event consumed by the last (?N) or . step. ClickHouse resets the anchor at .* to the event after the last match, so (?1).*(?t>0)(?2) can never match there and (?t<=N) directly after .* is vacuously true. A constraint before any (?N) or . is an error in the extension; ClickHouse measures it from wherever its implicit leading .* is.

5. sequenceMatchEvents reports an abandoned attempt. ClickHouse replaces its best chain only when a strictly longer one appears, so after a failed attempt it can report that attempt's timestamps for a later successful match of the same length ((?1)(?t==2) over c1@0, c1@3, c2@5 reports [0]). The extension reports the events that matched ([3]).

6. ClickHouse reads past its action list for a pattern ending in .*, (?t<…), (?t<=…) or (?t>=0) when the match consumes the last event (AggregateFunctionSequenceMatch.cpp, the trailing-skip loop has no end check). Its answer for such a group changed between runs; the extension's does not.

Other differences:

7. NULL inputs. ClickHouse skips a row with any NULL argument. The extension treats a NULL condition as false; a NULL timestamp skips the row; sequence_next_node keeps NULL values as events and can return NULL as the next value.

8. Units. Windows are DuckDB INTERVALs (month-based ones are rejected); timestamps are microseconds and (?t op N) thresholds are seconds, with the elapsed time floored to whole seconds ((?t==N) means [N, N+1)). Gaps touching DuckDB's ±infinity timestamps are computed exactly.

9. Accepted input. The extension accepts whitespace between pattern elements, a single 'strict' mode string, and two time constraints in a row after an event; ClickHouse rejects all three. ClickHouse accepts an empty pattern and thresholds above i64::MAX; the extension rejects both.

10. Empty input. Over zero rows retention returns [] and sequence_match / sequence_count return NULL; ClickHouse returns zeros.

11. sequenceNextNode condition limit. ClickHouse allows 64 event conditions; the extension 32.

CI/CD

The project uses GitHub Actions for continuous integration, end-to-end testing, and release management. All workflows are defined in .github/workflows/.

Workflows

CI (ci.yml)

Runs on every push to main and every pull request. 14 jobs ensure code quality across multiple dimensions. The workflow sets DUCKDB_DOWNLOAD_LIB=1 so test builds link a prebuilt libduckdb instead of compiling DuckDB from source.

CodeQL (codeql.yml)

Runs GitHub's CodeQL static analysis for Rust on every push to main, every pull request, and on a weekly schedule (Monday 06:00 UTC). Uses the security-and-quality query suite for comprehensive coverage.

• Triggers: push to main, PRs, weekly cron
• Language: Rust
• Action version: github/codeql-action v4.36.2 (SHA-pinned)
• Permissions: security-events: write (required to upload SARIF results)

Prerequisite — Disable Default Setup:

This workflow uses CodeQL's "advanced setup" (explicit workflow file). GitHub does not allow both Default Setup and advanced setup to be active simultaneously. If Default Setup is enabled, the SARIF upload will fail with:

CodeQL analyses from advanced configurations cannot be processed when the default setup is enabled

The workflow includes a pre-flight check that detects this conflict and fails fast with actionable remediation steps. To resolve:

1. Go to Settings → Code security → Code scanning → CodeQL analysis
2. Click the ⋯ menu → Disable CodeQL
3. Or via CLI: gh api --method PATCH repos/OWNER/REPO/code-scanning/default-setup -f state=not-configured

E2E Tests (e2e.yml)

Runs on every push to main and every pull request. Builds the extension using the community extension Makefile and tests it against a real DuckDB instance.

Test coverage:

• Extension loading verification
• All 8 functions (sessionize, retention, window_funnel, window_funnel_events, sequence_match, sequence_count, sequence_match_events, sequence_next_node)
• Mode parameters (strict_increase)
• GROUP BY aggregation
• Load test with 100K events across all aggregate functions
• No-match and empty-input edge cases

Platforms tested: Linux x86_64, macOS ARM64

Compatibility job (compat): The extension is stamped for the stable C API (ABI type C_STRUCT, C API v1.2.0). The compat job downloads the Linux artifact built by the E2E job, loads it into DuckDB v1.3.2, v1.4.4, v1.5.0, v1.5.6, and v2.0.0, and compares a query's output against the expected result.

Release (release.yml)

Triggered on git tag push (v*) or manual dispatch. Builds the extension for 4 platform targets, runs SQL integration tests, and creates a GitHub release with SHA256 checksums and build provenance attestations.

Build targets:

Supply chain security:

• SHA256 checksums for all artifacts
• GitHub artifact attestation via actions/attest-build-provenance v4.1.0 (SHA-pinned)
• Build artifacts retained for 90 days
• Build provenance tied to specific git commit

Community Submission (community-submission.yml)

On-demand workflow for preparing updates to the extension listing in the DuckDB Community Extensions repository. The extension was accepted via PR #1306 (merged 2026-02-15). This workflow is used for subsequent version updates. Triggered via workflow_dispatch with a dry_run toggle.

Phases:

Usage:

# Dry run — validate everything without making changes
gh workflow run community-submission.yml -f dry_run=true

# Full run — validate, build, test, pin ref, generate submission package
gh workflow run community-submission.yml -f dry_run=false

After a full run, the workflow summary contains the exact gh CLI commands to create a branch in the duckdb/community-extensions fork, update the ref, and open a PR — ensuring deterministic, repeatable version updates.

Pages (pages.yml)

Deploys mdBook documentation to GitHub Pages on push to main. Uses mdBook v0.4.40 with custom CSS styling.

Reproducing CI Locally

# Run the same checks as CI
cargo check --all-targets
DUCKDB_DOWNLOAD_LIB=1 cargo test   # 547 unit + 28 integration + 1 doc-test
cargo clippy --all-targets -- -D warnings
cargo fmt --all -- --check
RUSTDOCFLAGS=-Dwarnings cargo doc --no-deps --document-private-items
cargo deny check advisories bans licenses sources

# Build extension (requires submodule); do not set DUCKDB_DOWNLOAD_LIB here
git submodule update --init --recursive
make configure
make release

# Run SQL integration tests
make test_release

Security & Supply Chain

This page documents the security model and supply chain integrity measures for the duckdb-behavioral extension.

Versioning

This project follows Semantic Versioning (SemVer).

Version format: MAJOR.MINOR.PATCH (e.g., 0.1.0, 1.0.0, 1.2.3)

Pre-1.0 policy: While the version is 0.x.y, MINOR bumps may include breaking changes per SemVer section 4.

Version sources: The version is maintained in three files that must stay in sync:

• Cargo.toml (version = "X.Y.Z")
• description.yml (version: X.Y.Z)
• Git tag (vX.Y.Z)

The release workflow validates that all three match before building.

Dependency Audit

Runtime Dependencies

The extension has exactly two runtime dependencies:

Both versions are pinned exactly to prevent silent dependency updates. quack-rs provides entry_point_v2!, Connection/Registrar trait, AggregateFunctionSetBuilder, FfiState<T>, VectorReader/VectorWriter, the aggregate_{update,combine,finalize}_callback! macros (panic guard that reports a panic as a SQL error), and AggregateTestHarness. libduckdb-sys with the loadable-extension feature provides runtime function pointer stubs via global atomic statics.

Dev-Only Dependencies

These are used only by tests and benchmarks and are NOT linked into the release extension binary:

License Compliance

All dependencies are audited via cargo-deny in CI. The allow list in deny.toml:

Allowed licenses: MIT, Apache-2.0, BSD-3-Clause, CDLA-Permissive-2.0,
ISC, Unicode-3.0, Zlib

One crate-scoped exception allows CC0-1.0 for tiny-keccak, a lockfile-only transitive entry that is not part of any buildable dependency graph.

Build Integrity

Reproducible Builds

Release builds use the following Cargo profile for deterministic output:

[profile.release]
opt-level = 3
lto = true          # Full link-time optimization
codegen-units = 1   # Single codegen unit
panic = "unwind"    # Required: quack-rs turns callback panics into SQL errors
strip = true        # Strip debug symbols

Using codegen-units = 1 and lto = true reduces non-determinism from parallel compilation. The Cargo.lock file is committed to the repository to pin all transitive dependency versions.

Build Provenance

Release artifacts include:

1. SHA256 checksums (SHA256SUMS.txt) for every release artifact
2. GitHub artifact attestations via actions/attest-build-provenance v4.1.0 (SHA-pinned), which provides a cryptographic link between the artifact and the GitHub Actions build that produced it
3. Immutable build logs in GitHub Actions with full command output

Verification

# Verify downloaded artifact checksum
sha256sum -c SHA256SUMS.txt

# Verify GitHub attestation (requires gh CLI)
gh attestation verify behavioral-v0.10.1-linux_amd64.tar.gz \
  --repo tomtom215/duckdb-behavioral

Code Safety

Unsafe Code Confinement

All hand-written unsafe code is confined to the 9 files in src/ffi/ (the quack_rs::entry_point_v2! macro in src/lib.rs generates the extern "C" entry point). Business logic in src/sessionize.rs, src/retention.rs, src/window_funnel.rs, src/sequence.rs, src/sequence_next_node.rs, and src/pattern/ is 100% safe Rust.

Every callback's unsafe block has a // SAFETY: comment explaining why the invariants are upheld. The aggregate update, combine, and finalize callbacks are generated by quack-rs macros that catch panics and report them as SQL errors; this relies on panic = "unwind" in the release profile.

No Network Access

The extension makes zero network calls at runtime. It operates purely on data provided by DuckDB through the C API.

No File System Access

The extension does not read from or write to the file system. All state is managed in-memory through DuckDB's aggregate function lifecycle.

Extension Loading Security

DuckDB requires the -unsigned flag (or allow_unsigned_extensions) to load locally-built extensions, and only loads files ending in .duckdb_extension that carry the metadata footer (make configure release produces build/release/behavioral.duckdb_extension). The community extension distribution path uses DuckDB's built-in extension signing and verification system, which the build system handles automatically.

Reporting Vulnerabilities

Report security issues via GitHub Issues at github.com/tomtom215/duckdb-behavioral/issues.

Benchmarking

This page documents the benchmark infrastructure, methodology, and reproduction instructions for the duckdb-behavioral extension.

Methodology

All benchmarks use Criterion.rs with the following configuration:

• Sample size: 100 samples (default), reduced to 10 for inputs >= 100M
• Confidence level: 95%
• Measurement time: Extended to 30-60 seconds for large inputs
• Outlier detection: Criterion's built-in outlier classification
• Iterations: Criterion automatically determines iteration count
• Warm-up: Criterion's default warm-up period

Results are reported with confidence intervals. A result is considered statistically significant only when 95% confidence intervals do not overlap.

Benchmark Suite

Seven benchmark files cover the Rust state of every aggregate function. They are microbenchmarks of update/combine/finalize, not end-to-end SQL queries:

Scale Limits

Event-collecting functions store events in a Vec<Event> (16 bytes per event). At 100 million events, this requires 1.6 GB of memory. Criterion clones the data per iteration, requiring approximately 3.2 GB total. On a 21 GB system, 100 million is the practical maximum for event-collecting benchmarks.

O(1)-state functions (sessionize, retention) store no per-event data. sessionize_update runs up to 1 billion elements; retention_combine is currently capped at 100 million (a 1-billion run was measured once, in PERF.md Session 4).

sequence_next_node uses 32-byte NextNodeEvent structs with Arc<str> string storage, further reducing the maximum feasible scale.

Running Benchmarks

# Run all benchmarks (may require several hours for full suite)
cargo bench

# Run a specific benchmark
cargo bench --bench sessionize_bench
cargo bench --bench retention_bench
cargo bench --bench window_funnel_bench
cargo bench --bench sequence_bench
cargo bench --bench sort_bench
cargo bench --bench sequence_next_node_bench
cargo bench --bench sequence_match_events_bench

# Run a specific benchmark group
cargo bench --bench sequence_bench -- sequence_match

# Compile benchmarks without running (for CI)
cargo bench --no-run

Reading Results

Criterion generates HTML reports in target/criterion/. Open target/criterion/report/index.html in a browser for interactive charts.

Command-line output includes:

sequence_match/100000000
    time:   [1.0521 s 1.0789 s 1.1043 s]
    thrpt:  [90.551 Melem/s 92.687 Melem/s 95.049 Melem/s]

The three values in brackets represent the lower bound, point estimate, and upper bound of the 95% confidence interval.

Baseline Validation Protocol

Before any optimization work:

1. Run cargo bench 3 times to establish a stable baseline
2. Compare against the documented baseline in PERF.md
3. Environment differences (CPU, memory, OS) produce different absolute numbers but the same relative improvements

After optimization:

1. Run cargo bench 3 times with the optimization
2. Compare 95% CIs against the pre-optimization baseline
3. Accept only when CIs do not overlap (statistically significant)
4. If CIs overlap, revert and document as a negative result

Benchmark Data Generation

Each benchmark generates synthetic data with realistic patterns:

• Sessionize: Events every 5 minutes with a 33-minute gap every 10 events (simulates real session boundaries)
• Retention: Rotating true conditions per event
• Window funnel: Conditions fire for 1/3 of events in round-robin
• Sequence: Alternating condition patterns for match/count testing
• Sort: Reverse-order timestamps with jitter (worst case), and forward-order (best case for presorted detection)
• Sequence next node: Unique strings per event (worst case) and pooled 100-value cardinality (realistic case)

Contributing

Guidelines for contributing to duckdb-behavioral.

Development Setup

Prerequisites

• Rust 1.88+ (the project's MSRV)
• A C toolchain/linker (gcc or clang)
• Python 3 and make (for make configure release)
• DuckDB CLI v1.3.2 or later for E2E testing (CI checks v1.3.2, v1.4.4, v1.5.0, v1.5.6, v2.0.0)

Building

git clone --recurse-submodules https://github.com/tomtom215/duckdb-behavioral.git
cd duckdb-behavioral
cargo build

Running Tests

# 547 unit tests + 28 in-process integration tests + 1 doc-test.
# DUCKDB_DOWNLOAD_LIB=1 links a prebuilt libduckdb (the `duckdb` dev-dependency
# has no `bundled` feature, so a bare `cargo test` fails to link). The unit
# tests take about 2 s; the integration tests build and LOAD the release
# cdylib, 20-35 s when it must be rebuilt.
DUCKDB_DOWNLOAD_LIB=1 cargo test

# Clippy (zero warnings required)
cargo clippy --all-targets

# Format check
cargo fmt -- --check

All three checks must pass before submitting changes.

Code Style

Lint Standards

The project enforces zero clippy warnings with pedantic, nursery, and cargo lint groups enabled. See Cargo.toml [lints.clippy] for the full configuration and allowed exceptions (FFI callbacks, analytics math casts).

Architecture

• Pure Rust core: Business logic in top-level modules (sessionize.rs, retention.rs, etc.) with zero FFI dependencies.
• FFI bridge via quack-rs SDK: DuckDB C API registration confined to src/ffi/, using quack-rs v0.19.0 for safe builders (including returns_logical(LogicalType) for LIST(T) returns), state management (FfiState<T>), vector I/O (VectorReader/VectorWriter), LIST output (ListVector), and type construction (LogicalType::list()). The aggregate update, combine, and finalize callbacks are generated with quack-rs's aggregate_{update,combine,finalize}_callback! macros, which turn a panic into a SQL error (the release profile keeps panic = "unwind" for this). Every callback's unsafe block has a // SAFETY: comment.
• All public items documented: Functions, structs, and modules must have doc comments.

Adding a New Function

1. Create src/new_function.rs with state struct implementing Default + Send + 'static, plus update(), combine_in_place(), finalize() methods.
2. Add impl quack_rs::aggregate::AggregateState for NewFunctionState {} in the FFI module.
3. Create src/ffi/new_function.rs using quack-rs builders:
   • Use AggregateFunctionSetBuilder::new("name").overloads(...) for registration (automatically handles function-set-name per overload).
   • Use FfiState::<NewFunctionState>::size_callback/init_callback/destroy_callback.
   • Write update/combine/finalize with quack_rs::aggregate_update_callback!, aggregate_combine_callback!, and aggregate_finalize_callback!.
   • Use VectorReader for input (read_bool, read_str, read_i64, read_interval).
   • Use VectorWriter for output (write_i32, write_bool, write_varchar, set_null).
   • For LIST(T) output, use .returns_logical(LogicalType::list(TypeId::...)) on the builder, and ListVector + VectorWriter for child data in finalize.
   • CRITICAL: combine_in_place must propagate all configuration fields from the source state (not just events).
4. Register in src/ffi/mod.rs register_all().
5. Add pub mod new_function; to src/lib.rs.
6. Add a benchmark in benches/.
7. Write unit tests + AggregateTestHarness combine config-propagation tests.
8. E2E test: Build release, load in DuckDB CLI, verify SQL produces correct results.

Testing Expectations

Unit Tests

• Cover state lifecycle: empty state, single update, multiple updates, finalize.
• Cover edge cases: threshold boundaries, NULL handling, empty inputs.
• Cover combine correctness: empty combine, boundary detection, associativity.
• Cover the DuckDB zero-initialized target combine pattern (DuckDB creates fresh states and combines source states into them).
• Property-based tests (proptest) for algebraic properties where applicable.

E2E Tests

Unit tests validate business logic in isolation. E2E tests validate the FFI boundary, DuckDB's data chunk format, state lifecycle management, and the extension loading mechanism. Both levels are mandatory.

# Build release and stamp the metadata footer
# (-> build/release/behavioral.duckdb_extension)
make configure release
duckdb -unsigned -c "LOAD 'build/release/behavioral.duckdb_extension'; SELECT ..."

# Or by hand: copy and append metadata (stable C API v1.2.0, default ABI type C_STRUCT)
cargo build --release
cp target/release/libbehavioral.so /tmp/behavioral.duckdb_extension
python3 extension-ci-tools/scripts/append_extension_metadata.py \
  -l /tmp/behavioral.duckdb_extension -n behavioral \
  -p linux_amd64 -dv v1.2.0 -ev v0.10.1 \
  -o /tmp/behavioral.duckdb_extension
duckdb -unsigned -c "LOAD '/tmp/behavioral.duckdb_extension'; SELECT ..."

# SQL logic tests in test/sql/
make test_release

DuckDB loads only .duckdb_extension files with the metadata footer; it refuses the raw libbehavioral.so/.dylib.

Benchmark Protocol

Performance claims must follow the Session Improvement Protocol documented in PERF.md:

1. Baseline first: Run cargo bench 3 times before any changes. Record baseline with confidence intervals.
2. One optimization per commit: Each optimization must be independently measurable.
3. Non-overlapping CIs required: Accept only when 95% confidence intervals do not overlap between before and after measurements.
4. Negative results documented: If an optimization produces overlapping CIs or regression, revert it and document the result honestly in PERF.md.
5. Update documentation: Add an optimization history entry to PERF.md and update the current baseline table.

Running Benchmarks

# Run all benchmarks
cargo bench

# Run a specific group
cargo bench -- sessionize
cargo bench -- sequence_match_events

# Results stored in target/criterion/

PR Process

1. Create a feature branch from main.
2. Make changes following the code style and testing expectations above.
3. Ensure all checks pass: DUCKDB_DOWNLOAD_LIB=1 cargo test, cargo clippy --all-targets, cargo fmt -- --check.
4. If performance-related, include Criterion benchmark data with confidence intervals.
5. Update documentation if function signatures, test counts, or benchmark numbers changed.
6. Submit a pull request with a clear description of changes.

Documentation

• CLAUDE.md: AI assistant context. Update test counts, benchmark counts, session entries, and lessons learned when making significant changes.
• PERF.md: Performance optimization history. Follow the session protocol.
• README.md: Public-facing documentation. Keep performance tables and test counts current.
• docs/: GitHub Pages documentation (mdBook). Keep function signatures and performance claims in sync with the source code.