Cohort Pipeline - SQL Programming

A CTE pipeline can build a cohort report by filtering signups, joining events, and summarizing activity.

Program

Play the script to choose a signup cohort and count users with activity.

cohort_pipeline.sql

CREATE TABLE signups (user_id INTEGER, cohort TEXT);
INSERT INTO signups VALUES (1, 'Jan'), (2, 'Jan'), (3, 'Feb'), (4, 'Feb');
CREATE TABLE events (user_id INTEGER, event_name TEXT);
INSERT INTO events VALUES (1, 'view'), (1, 'buy'), (2, 'view'), (3, 'view'), (4, 'buy');
WITH params(wanted_cohort) AS (VALUES ()), cohort_users AS (SELECT user_id FROM signups WHERE cohort = (SELECT wanted_cohort FROM params)), joined_events AS (SELECT cohort_users.user_id, events.event_name FROM cohort_users LEFT JOIN events ON events.user_id = cohort_users.user_id), user_counts AS (SELECT user_id, COUNT(event_name) AS event_count FROM joined_events GROUP BY user_id) SELECT user_id, event_count, event_count > 0 AS active FROM user_counts ORDER BY user_id;

CREATE TABLE signups (user_id INTEGER, cohort TEXT);
INSERT INTO signups VALUES (1, 'Jan'), (2, 'Jan'), (3, 'Feb'), (4, 'Feb');
CREATE TABLE events (user_id INTEGER, event_name TEXT);
INSERT INTO events VALUES (1, 'view'), (1, 'buy'), (2, 'view'), (3, 'view'), (4, 'buy');
WITH params(wanted_cohort) AS (VALUES ()), cohort_users AS (SELECT user_id FROM signups WHERE cohort = (SELECT wanted_cohort FROM params)), joined_events AS (SELECT cohort_users.user_id, events.event_name FROM cohort_users LEFT JOIN events ON events.user_id = cohort_users.user_id), user_counts AS (SELECT user_id, COUNT(event_name) AS event_count FROM joined_events GROUP BY user_id) SELECT user_id, event_count, event_count > 0 AS active FROM user_counts ORDER BY user_id;

cohort_users The first CTE selects users in the chosen signup cohort.

joined_events The second CTE attaches matching events while keeping cohort users.

user_counts The final CTE summarizes activity per user.