Analytical CTE Pipelines

Cohort Pipeline

Count Active Users

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.

wanted_cohort
cohort_pipeline.sql
Replay: real traced execution (multi-file project)
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 ('Jan')), 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 ('Feb')), 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;

  1. signups ← 0 rows

    1CREATE TABLE signups (user_id INTEGER, cohort TEXT);2INSERT INTO signups VALUES (1, 'Jan'), (2, 'Jan'), (3, 'Feb'), (4, 'Feb');
    values this step0 rowssignups

  2. tables ← 2 rows

    2INSERT INTO signups VALUES (1, 'Jan'), (2, 'Jan'), (3, 'Feb'), (4, 'Feb');3CREATE TABLE events (user_id INTEGER, event_name TEXT);4INSERT INTO events VALUES (1, 'view'), (1, 'buy'), (2, 'view'), (3, 'view'), (4, 'buy');
    values this step2 rowstables

  3. events ← 5 rows

    3CREATE TABLE events (user_id INTEGER, event_name TEXT);4INSERT INTO events VALUES (1, 'view'), (1, 'buy'), (2, 'view'), (3, 'view'), (4, 'buy');5WITH params(wanted_cohort) AS (VALUES ('Jan')), 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;
    values this step5 rowsevents

  4. result ← 2 rows

    4INSERT INTO events VALUES (1, 'view'), (1, 'buy'), (2, 'view'), (3, 'view'), (4, 'buy');5WITH params(wanted_cohort) AS (VALUES ('Jan')), 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;
    values this step2 rowsresult

  1. signups ← 0 rows

    1CREATE TABLE signups (user_id INTEGER, cohort TEXT);2INSERT INTO signups VALUES (1, 'Jan'), (2, 'Jan'), (3, 'Feb'), (4, 'Feb');
    values this step0 rowssignups

  2. tables ← 2 rows

    2INSERT INTO signups VALUES (1, 'Jan'), (2, 'Jan'), (3, 'Feb'), (4, 'Feb');3CREATE TABLE events (user_id INTEGER, event_name TEXT);4INSERT INTO events VALUES (1, 'view'), (1, 'buy'), (2, 'view'), (3, 'view'), (4, 'buy');
    values this step2 rowstables

  3. events ← 5 rows

    3CREATE TABLE events (user_id INTEGER, event_name TEXT);4INSERT INTO events VALUES (1, 'view'), (1, 'buy'), (2, 'view'), (3, 'view'), (4, 'buy');5WITH params(wanted_cohort) AS (VALUES ('Feb')), 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;
    values this step5 rowsevents

  4. result ← 2 rows

    4INSERT INTO events VALUES (1, 'view'), (1, 'buy'), (2, 'view'), (3, 'view'), (4, 'buy');5WITH params(wanted_cohort) AS (VALUES ('Feb')), 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;
    values this step2 rowsresult
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.