SQL Event Modeling — Applied Session IDs and Recursive CTEs

WITH with_lag AS (
  -- LAG() OVER: a window function that gets the previous row's value in the specified order (ORDER BY)
  -- PARTITION BY: calculate independently per user (never cross a user boundary)
  SELECT
    user_id, event_type, event_time,
    LAG(event_time) OVER (
      PARTITION BY user_id ORDER BY event_time
    ) AS prev_event_time
  FROM   user_events
),
with_flag AS (
  -- CASE WHEN ... THEN ... ELSE ... END: basic conditional syntax
  -- EXTRACT(EPOCH FROM ...): convert a time interval to a number of seconds (epoch seconds)
  SELECT
    user_id, event_type, event_time,
    CASE
      WHEN prev_event_time IS NULL
        OR EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 60 >= 30
      THEN 1 ELSE 0
    END AS is_new_session
  FROM   with_lag
),
with_session_num AS (
  -- SUM() OVER: calculate the cumulative sum of matching values (here, the session-start flag 1)
  -- ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: the frame from the first row through the current row
  SELECT
    user_id, event_type, event_time,
    SUM(is_new_session) OVER (                                            -- cumulative flag sum = session number
      PARTITION BY user_id
      ORDER BY     event_time
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_num
  FROM   with_flag
)
SELECT
  user_id,
  user_id || '-' || session_num  AS session_id,                           -- || operator: concatenate strings (for example, '1' || '-' || '1' -> '1-1')
  event_type,
  TO_CHAR(event_time, 'HH24:MI') AS event_time
FROM   with_session_num
ORDER BY user_id, event_time;

/*
  Execution order (logical SQL evaluation order):
  1. CTE with_lag
  2. CTE with_flag
  3. CTE with_session_num
  4. Outer query
  */

WITH user_cohort AS (
  SELECT                                                                                     -- first month per user = cohort
    user_id,
    DATE_TRUNC('month', MIN(event_time))::date  AS cohort_month  -- truncate to month → DATE type
  FROM   user_events
  GROUP BY user_id
),
user_activity AS (
  SELECT DISTINCT                                                                            -- months when each user was active (deduplicated)
    user_id,
    DATE_TRUNC('month', event_time)::date  AS activity_month
  FROM   user_events
),
cohort_join AS (
  SELECT                                                                                     -- attach elapsed months to each activity row
    uc.cohort_month,
    ua.activity_month,
    ua.user_id,
    ((EXTRACT(YEAR FROM ua.activity_month) - EXTRACT(YEAR FROM uc.cohort_month)) * 12  -- months elapsed since signup (year difference × 12 + month difference)
      + (EXTRACT(MONTH FROM ua.activity_month) - EXTRACT(MONTH FROM uc.cohort_month)))::int
        AS months_since_signup
  FROM        user_cohort   uc                                                               -- USING (user_id): shorthand JOIN on the column shared by both tables
  JOIN        user_activity ua USING (user_id)
)
SELECT
  cohort_month,
  months_since_signup,
  COUNT(DISTINCT user_id)            AS active_users,                                        -- number of active users in each month
  FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (                      -- FIRST_VALUE() OVER: get the value in the first row of the specified order / the first value by elapsed month is the initial cohort size
    PARTITION BY cohort_month ORDER BY months_since_signup
  )                                       AS cohort_size,
  ROUND(100.0 * COUNT(DISTINCT user_id)                            -- calculate retention as active users / initial cohort size
       / FIRST_VALUE(COUNT(DISTINCT user_id)) OVER (
           PARTITION BY cohort_month ORDER BY months_since_signup
         ), 1)                        AS retention_pct
FROM     cohort_join
GROUP BY cohort_month, months_since_signup
ORDER BY cohort_month, months_since_signup;

/*
  Execution order (logical SQL evaluation order):
  1. CTE user_cohort
  2. CTE user_activity
  3. CTE cohort_join
  4. Outer query
  5. 100.0 * active / cohort_size  → retention rate (%)
  */

WITH user_paths AS (
  -- Stage 1: group by user and concatenate events chronologically
  SELECT
    user_id,
    STRING_AGG(event_type, ' → ' ORDER BY event_time) AS path,      -- STRING_AGG(column, delimiter ORDER BY order): concatenate strings while guaranteeing order
    COUNT(*)                                        AS path_length  -- COUNT(*): count the total events generated by the user
  FROM   events
  GROUP BY user_id
)
-- Stage 2: aggregate user counts by identical path (behavioral pattern)
SELECT
  path,
  COUNT(*)         AS user_count,                                   -- COUNT(*) counts rows grouped by path (= users sharing the path)
  MAX(path_length) AS path_length                                   -- MAX(): maximum in the group; for an identical path, MAX() and MIN() give the same length
FROM   user_paths
GROUP BY path
ORDER BY user_count DESC, path_length DESC;                         -- ORDER BY column DESC: sort from larger values to smaller values

/*
  Execution order (logical SQL evaluation order):
  1. CTE user_paths
  2. Outer query
  */

-- WITH RECURSIVE: declare a recursive CTE (self-reference becomes possible)
WITH RECURSIVE date_series AS (
  -- Stage 1: base case — return the start date as one row
  SELECT DATE '2024-01-10' AS day
  -- UNION ALL: combine SELECT results vertically while preserving duplicates
  UNION ALL
  -- Stage 2: recursive term — add one day to the most recent result
  SELECT day + 1
  FROM   date_series
  WHERE  day < DATE '2024-01-15'                         -- continue only while day is before 2024-01-15
),
daily_dau AS (
  -- truncate event times to days and count distinct users per day
  SELECT
    DATE_TRUNC('day', event_time)::DATE AS day,
    COUNT(DISTINCT user_id)             AS active_users  -- DISTINCT user_id: count each user only once
  FROM   events
  GROUP BY DATE_TRUNC('day', event_time)::DATE
)
-- LEFT JOIN from the date series and turn unmatched-day NULLs into 0
SELECT
  ds.day,
  COALESCE(dd.active_users, 0) AS active_users           -- COALESCE(value1, value2): return value2 when value1 is NULL; standard missing-value fill
FROM      date_series ds                                 -- LEFT JOIN: keep every left-table row; join only matching right-table rows
LEFT JOIN daily_dau   dd ON ds.day = dd.day
ORDER BY  ds.day;

/*
  Execution order (logical SQL evaluation order):
  1. CTE date_series (WITH RECURSIVE)
  2. CTE daily_dau
  3. Outer query
  */