Behavioral analytics functions for DuckDB, inspired by ClickHouse
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
| author | commit_time | work_session |
|---|---|---|
| alice | 2024-03-15 09:00 | 1 |
| alice | 2024-03-15 09:45 | 1 |
| alice | 2024-03-15 10:30 | 1 |
| alice | 2024-03-15 16:00 | 2 |
| bob | 2024-03-15 14:00 | 1 |
| bob | 2024-03-16 10:00 | 2 |
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
| author | retained |
|---|---|
| alice | [true, true, true, false] |
| bob | [true, true, false, false] |
| carol | [true, true, true, false] |
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
| author | pr_funnel_step |
|---|---|
| alice | 2 |
| bob | 2 |
| carol | 1 |
Supports 6 combinable modes: strict_deduplication, strict_order, strict_increase, strict_once, allow_reentry, timestamp_dedup.
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
| author | has_bug_pattern |
|---|---|
| alice | true |
| bob | true |
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
| author | fail_fix_cycles |
|---|---|
| alice | 0 |
| bob | 2 |
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_id | lifecycle_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] |
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
| author | next_file |
|---|---|
| alice | src/executor.rs |
| bob | src/ast.rs |
| carol | NULL |
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
| Function | Use Case | Reference |
|---|---|---|
sessionize | Work session detection | Eyolfson, Tan, Lam. MSR 2011 |
retention | Contributor cohorts | Casalnuovo et al. ESEC/FSE 2015 |
window_funnel | Onboarding funnels | Casalnuovo et al. ESEC/FSE 2015 |
sequence_match | Bug pattern detection | Eyolfson et al. MSR 2011 |
sequence_count | CI/CD failure cycles | Vasilescu et al. ICSME 2014 |
sequence_match_events | PR lifecycle mining | Gousios et al. ICSE 2014 |
sequence_next_node | File co-change patterns | Gall, Hajek, Jazayeri. ICSM 1998 |
-- 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 (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';
| Function | Returns | Description |
|---|---|---|
sessionize(ts, interval) | BIGINT | Window function: session IDs |
retention(bool, ...) | BOOLEAN[] | Cohort retention analysis |
window_funnel(interval, ts, bool, ...) | INTEGER | Conversion funnel tracking |
window_funnel_events(interval, ts, bool, ...) | TIMESTAMP[] | Timestamps of the best funnel chain |
sequence_match(pattern, ts, bool, ...) | BOOLEAN | Pattern matching over events |
sequence_count(pattern, ts, bool, ...) | BIGINT | Count pattern matches |
sequence_match_events(pattern, ts, bool, ...) | TIMESTAMP[] | Matched step timestamps |
sequence_next_node(dir, base, ts, val, bool, ...) | VARCHAR | Next value after pattern match |