ROW_NUMBER() is a basic window function that assigns a sequence number to each row according to the specified PARTITION BY and ORDER BY. This makes it possible to extract each user's first event with the CTE + WHERE rn = 1 pattern.

ROW_NUMBER() OVER (
  PARTITION BY user_id          -- Reset for each user
  ORDER BY     event_time ASC  -- Oldest first → rn=1 is the first event
)
-- ASC → rn=1 is the first event (first touch)
-- DESC → rn=1 is the last event (last touch)

From the user_events table, extract exactly one row for the first event for each user (event_type and event_time). Return user_id, first_event_type, first_event_time in ascending user_id order.

※ user2 did not have a view; signup is its first event (direct signup from a landing page).

Expected output (ascending user_id):

user2's first event is signup. Only the first row for each user is retained.

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
  */

LAG() is a window function that references the value from the row immediately before the current row. In event modeling, it is used to calculate the time since the previous event and define a new session when the gap reaches a threshold such as 30 minutes.

LAG(event_time) OVER (
  PARTITION BY user_id      -- Never reference the previous row across users
  ORDER BY     event_time    -- Take the previous row after chronological sorting
)
-- Convert the timestamp difference to minutes
EXTRACT(EPOCH FROM (event_time - prev_event_time)) / 60
-- EPOCH = seconds; divide by 60 to convert to minutes

From the user_events table, calculate the gap in minutes from the previous event and add a session-start flag that is true when the gap is at least 30 minutes or the event is the first one. Return user_id, event_type, event_time, prev_event_time, gap_min, is_new_session in ascending user_id / event_time order.

※ user1: 10:03 (3 minutes later) → same session; 10:35 (32 minutes later) → new session. user2: 11:05 (5 minutes later) → same session; 12:10 (65 minutes later) → new session.

Expected output (ascending user_id / event_time):

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, event_time, 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
  */

LEAD() is the opposite-direction window function to LAG(): it references the value from the row immediately after the current row. By retrieving the event that occurs next, you can aggregate user behavior paths (transition patterns).

LEAD(event_type) OVER (
  PARTITION BY user_id      -- Never reference the next row across users
  ORDER BY     event_time    -- Take the next row after chronological sorting
)
-- The last event has no next row, so it returns NULL

From the user_events table, aggregate the transition patterns (from_event → to_event) for the event immediately following each event. Exclude final events (to_event = NULL), and return from_event, to_event, transition_count ordered by transition_count descending and from_event ascending.

Expected output (transition_count descending / from_event ascending):

view→click occurs 3 times: once for user1 plus twice for user2, making it the most common. click→view and view→purchase occur twice each. Final transitions (purchase→NULL and click→NULL) are excluded.

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 (Daily Active Users) and MAU (Monthly Active Users) are among the most important metrics in event modeling. Use DATE_TRUNC to round event_time to a day or month, then use COUNT(DISTINCT user_id) to calculate the number of unique users.

From the user_events table, calculate ① daily DAU (daily active users) and ② monthly MAU (monthly active users) separately. Return activity_date, dau for ① and activity_month, mau for ②, ordered by activity_date / activity_month ascending.

※ January: user1 visits twice on the same day but DAU=1. January MAU=4 (users 1–4). February MAU=3 (users 1, 3, and 5).

① DAU (daily active users):

Even though user1 was active twice on 2024-01-10, DAU=2 (user1+user2). COUNT DISTINCT removes duplicates.

② MAU (monthly active users):

January: users 1–2–3–4, so 4 users. February: users 1–3–5, so 3 users. Sum of January DAU (2+2+2=6) ≠ MAU (4).

-- ① 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
  */