SQL Event Modeling — Basics of Sessions and DAU/MAU

WITH ranked AS (
  SELECT
    user_id,
    event_type,
    event_time,
    ROW_NUMBER() OVER (          -- Window function: no GROUP BY
      PARTITION BY user_id        -- Reset numbering for each user
      ORDER BY     event_time ASC -- Number oldest first → rn=1 is the first
    ) AS rn
  FROM   user_events
)
SELECT
  user_id,
  event_type  AS first_event_type,
  event_time  AS first_event_time
FROM   ranked
WHERE  rn = 1           -- Keep only the first row for each user
ORDER BY user_id;

/*
  Execution order (logical SQL evaluation order):
  1. CTE ranked
  2. Outer query
  3. SELECT event_type AS first_event_type  → Rename the column
  4. ORDER BY user_id                       → Ascending user_id
  */

WITH with_lag AS (
  SELECT
    user_id, event_type, event_time,
    LAG(event_time) OVER (       -- Reference the previous event_time
      PARTITION BY user_id         -- Do not cross user boundaries
      ORDER BY     event_time       -- Chronological order
    ) AS prev_event_time
  FROM   user_events
)
SELECT
  user_id,
  event_type,
  TO_CHAR(event_time, 'HH24:MI') AS event_time,
  TO_CHAR(prev_event_time, 'HH24:MI') AS prev_event_time,
  ROUND(
    EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 60  -- Convert seconds to minutes
  )::int  AS gap_min,
  CASE
    WHEN prev_event_time IS NULL                                        -- First event
      OR EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 60 >= 30  -- Gap of at least 30 minutes
    THEN true
    ELSE false
  END AS is_new_session
FROM   with_lag
ORDER BY user_id, event_time;

/*
  Execution order (logical SQL evaluation order):
  1. CTE with_lag
  2. Outer query
  3. ORDER BY user_id, event_time  → Chronological order
  */

WITH transitions AS (
  SELECT
    user_id,
    event_type        AS from_event,
    LEAD(event_type)  OVER (   -- Reference the next event type
      PARTITION BY user_id       -- Do not cross user boundaries
      ORDER BY     event_time     -- Chronological order
    ) AS to_event
  FROM   user_events
)
SELECT
  from_event,
  to_event,
  COUNT(*) AS transition_count
FROM   transitions
WHERE  to_event IS NOT NULL    -- Exclude final events (no next event)
GROUP BY from_event, to_event
ORDER BY transition_count DESC, from_event;

/*
  Execution order (logical SQL evaluation order):
  1. CTE transitions
  2. Outer query
  3. ORDER BY transition_count DESC, from_event  → Most frequent first, then alphabetical
  */

-- ① DAU: Round event_time to the day and count unique users
SELECT
  DATE_TRUNC('day', event_time)::date  AS activity_date,
  COUNT(DISTINCT user_id)             AS dau
FROM   user_events
GROUP BY activity_date
ORDER BY activity_date;

-- ② MAU: Round event_time to the month and count unique users
SELECT
  DATE_TRUNC('month', event_time)::date  AS activity_month,
  COUNT(DISTINCT user_id)               AS mau
FROM   user_events
GROUP BY activity_month
ORDER BY activity_month;

/*
  Execution order (DAU query):
  1. FROM user_events          → Read the rows
  2. DATE_TRUNC('day', ...)    → Round event_time to the day
  3. GROUP BY activity_date    → Group by date
  4. COUNT(DISTINCT user_id)   → Count unique users per day
  5. ORDER BY activity_date    → Sort by date ascending

  Execution order (MAU query):
  1. FROM user_events          → Read the rows
  2. DATE_TRUNC('month', ...)  → Round event_time to the month
  3. GROUP BY activity_month   → Group by month
  4. COUNT(DISTINCT user_id)   → Count unique users per month
  5. ORDER BY activity_month   → Sort by month ascending
  */