behavioral DuckDB Extension

Behavioral analytics functions for DuckDB, inspired by ClickHouse

This extension provides 8 aggregate functions for user behavior analysis: sessionization, retention cohorts, conversion funnels, sequence pattern matching, and more. Six follow ClickHouse's semantics for easy migration; the differences are listed in the docs.

User Work Session Detection

sessionize Eyolfson et al., MSR 2011

Assign session IDs to events based on inactivity gaps. Events within the timeout window belong to the same session.

SELECT author, commit_time,
    sessionize(commit_time, INTERVAL '4 hours')
        OVER (PARTITION BY author ORDER BY commit_time)
        AS work_session
FROM git_commits
ORDER BY author, commit_time;

Result

authorcommit_timework_session
alice2024-03-15 09:001
alice2024-03-15 09:451
alice2024-03-15 10:301
alice2024-03-15 16:002
bob2024-03-15 14:001
bob2024-03-16 10:002

Contributor Retention Cohort Analysis

retention Casalnuovo et al., ESEC/FSE 2015

Track whether users remain active in subsequent periods. Each boolean in the result indicates retention for that period.

SELECT author,
    retention(
        active_month = first_month,
        active_month = first_month + 1,
        active_month = first_month + 2,
        active_month = first_month + 3
    ) AS retained
FROM developer_activity
GROUP BY author;

Result

authorretained
alice[true, true, true, false]
bob[true, true, false, false]
carol[true, true, true, false]

PR Lifecycle Funnel

window_funnel Gousios et al., ICSE 2014

Model a conversion funnel: how far does each user progress through ordered steps within a time window? Returns the furthest step reached.

SELECT author,
    window_funnel(
        INTERVAL '24 hours',
        event_time,
        event_type = 'pr_opened',
        event_type = 'first_review',
        event_type = 'approved',
        event_type = 'merged'
    ) AS pr_funnel_step
FROM pr_events
GROUP BY author;

Result

authorpr_funnel_step
alice2
bob2
carol1

Supports 6 combinable modes: strict_deduplication, strict_order, strict_increase, strict_once, allow_reentry, timestamp_dedup.

Bug-Introducing Commit Pattern Detection

sequence_match Eyolfson et al., MSR 2011

Match regex-like patterns over event sequences. Detect: large commit (>500 LOC) followed by bug-fix within 7 days.

SELECT author,
    sequence_match(
        '(?1)(?t<=604800)(?2)',
        commit_time,
        lines_changed > 500,
        is_bug_fix
    ) AS has_bug_pattern
FROM git_commits
GROUP BY author;

Result

authorhas_bug_pattern
alicetrue
bobtrue

CI/CD Failure Cycle Count

sequence_count Vasilescu et al., ICSME 2014

Count non-overlapping occurrences of push → ci_failure → push cycles per developer.

SELECT author,
    sequence_count(
        '(?1)(?2)(?3)',
        event_time,
        event_type = 'push',
        event_type = 'ci_failure',
        event_type = 'push'
    ) AS fail_fix_cycles
FROM ci_events
GROUP BY author;

Result

authorfail_fix_cycles
alice0
bob2

PR Review-to-Merge Timeline

sequence_match_events Gousios et al., ICSE 2014

Extract timestamps of each matched step in a sequence pattern. Returns a list of timestamps for the condition steps.

SELECT pr_id,
    sequence_match_events(
        '(?1)(?2)(?3)(?4)',
        event_time,
        event_type = 'pr_opened',
        event_type = 'first_review',
        event_type = 'approved',
        event_type = 'merged'
    ) AS lifecycle_timestamps
FROM pr_events
GROUP BY pr_id;

Result

pr_idlifecycle_timestamps
1001[03-15 10:00, 03-15 14:30, 03-16 09:00, 03-16 10:00]
1002[03-15 11:00, 03-15 11:30, 03-15 12:00, 03-15 12:15]
1003[03-17 08:00]

File Co-Change Pattern Mining

sequence_next_node Gall et al., ICSM 1998

Find what event value follows a pattern match. Discover which file developers modify after touching src/parser.rs.

SELECT author,
    sequence_next_node(
        'forward', 'first_match',
        commit_time, file_path,
        file_path = 'src/parser.rs',
        file_path = 'src/parser.rs'
    ) AS next_file
FROM file_changes
GROUP BY author;

Result

authornext_file
alicesrc/executor.rs
bobsrc/ast.rs
carolNULL

All 7 examples above are grounded in published software engineering research. The extension turns DuckDB into a behavioral analytics engine for git repository mining, product analytics, and user behavior analysis.

Academic References

FunctionUse CaseReference
sessionizeWork session detectionEyolfson, Tan, Lam. MSR 2011
retentionContributor cohortsCasalnuovo et al. ESEC/FSE 2015
window_funnelOnboarding funnelsCasalnuovo et al. ESEC/FSE 2015
sequence_matchBug pattern detectionEyolfson et al. MSR 2011
sequence_countCI/CD failure cyclesVasilescu et al. ICSME 2014
sequence_match_eventsPR lifecycle miningGousios et al. ICSE 2014
sequence_next_nodeFile co-change patternsGall, Hajek, Jazayeri. ICSM 1998

Quick Start (Community Extension)

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

-- Try it out
SELECT sessionize(ts, INTERVAL '30 minutes')
    OVER (ORDER BY ts)
FROM events;

Build from Source

-- Build from source (adds the metadata footer DuckDB requires)
git submodule update --init --recursive
make configure release

-- Load the locally-built extension (requires -unsigned)
duckdb -unsigned
LOAD 'build/release/behavioral.duckdb_extension';

All Functions

FunctionReturnsDescription
sessionize(ts, interval)BIGINTWindow function: session IDs
retention(bool, ...)BOOLEAN[]Cohort retention analysis
window_funnel(interval, ts, bool, ...)INTEGERConversion funnel tracking
window_funnel_events(interval, ts, bool, ...)TIMESTAMP[]Timestamps of the best funnel chain
sequence_match(pattern, ts, bool, ...)BOOLEANPattern matching over events
sequence_count(pattern, ts, bool, ...)BIGINTCount pattern matches
sequence_match_events(pattern, ts, bool, ...)TIMESTAMP[]Matched step timestamps
sequence_next_node(dir, base, ts, val, bool, ...)VARCHARNext value after pattern match