QUESTION 6’s reach-based funnel did not consider order, but in practice a strict funnel may be required to evaluate only users who progressed in the order view → click → purchase.

-- Get the first timestamp for each step
MIN(CASE WHEN event_type = 'view' THEN event_time END) AS view_time

-- Check whether the next step occurs at or after the previous step
CASE WHEN view_time <= click_time THEN 1 ELSE 0 END

From the user_events table, calculate the number of users who passed through each step (view → click → purchase) in order. Return one row with the output columns total_users, view_users, click_users, purchase_users.

Note: user2 clicked before viewing; user4 purchased before clicking.

Expected output (one row):

user1 followed the full order through purchase. user4 followed the order through click, but purchase was out of order. user2 has only view because click occurred out of order.

WITH user_times AS (
  SELECT
    user_id,
    MIN(CASE WHEN event_type = 'view'     THEN event_time END) AS view_time,  -- time of the first view
    MIN(CASE WHEN event_type = 'click'    THEN event_time END) AS click_time,
    MIN(CASE WHEN event_type = 'purchase' THEN event_time END) AS purchase_time
  FROM   user_events
  GROUP BY user_id
)
SELECT
  COUNT(*) AS total_users,
  SUM(CASE WHEN view_time IS NOT NULL THEN 1 ELSE 0 END) AS view_users,
  SUM(CASE WHEN view_time <= click_time THEN 1 ELSE 0 END) AS click_users,  -- satisfies the view→click order
  SUM(CASE WHEN view_time <= click_time
            AND click_time <= purchase_time THEN 1 ELSE 0 END) AS purchase_users  -- view→click→purchase order
FROM   user_times;

/*
  Logical execution order:
  1. CTE user_times
  2. Outer query
  */

Cohort analysis tracks the behavior of user groups (cohorts) who signed up around the same time. Aggregating whether users were active within 30 days of signup by signup month makes it possible to determine whether an improvement initiative affected a particular cohort.

-- Add an INTERVAL condition to the ON clause to limit the period
LEFT JOIN user_events e
  ON  c.user_id      = e.user_id
  AND e.event_time  >= c.signup_date
  AND e.event_time   < c.signup_date + INTERVAL '30 days'

From the users and user_events tables, calculate the cohort size, active users with an event within 30 days of signup, and retention rate for each signup-month cohort. Return the output columns cohort_month, cohort_size, active_users, retention_pct, ordered by cohort_month ascending.

Note: user4’s event (3/15) is more than 30 days after signup_date (2/10); user5 has no events.

Expected output (cohort_month ascending):

Both users in the January cohort (user1 and user2) are active. Only user3 is active in the February cohort (user3–user5).

WITH cohorts AS (                                              -- Define the cohort month
  SELECT
    user_id,
    signup_date,
    DATE_TRUNC('month', signup_date)::date  AS cohort_month
  FROM   users
),
activity AS (                                                -- Activity within 30 days
  SELECT
    c.user_id,
    c.cohort_month,
    COUNT(e.event_time)  AS event_count         -- 0 = inactive
  FROM       cohorts c
  LEFT JOIN  user_events e
    ON  c.user_id      = e.user_id
    AND e.event_time  >= c.signup_date
    AND e.event_time   < c.signup_date + INTERVAL '30 days'  -- Time-window condition in ON clause
  GROUP BY c.user_id, c.cohort_month
)
SELECT
  cohort_month,
  COUNT(*)                                                    AS cohort_size,
  COUNT(*) FILTER (WHERE event_count > 0)                   AS active_users,
  ROUND(
    COUNT(*) FILTER (WHERE event_count > 0) * 100.0
    / NULLIF(COUNT(*), 0), 1)                              AS retention_pct
FROM   activity
GROUP BY cohort_month
ORDER BY cohort_month;

/*
  Logical execution order:
  1. CTE cohorts
  2. CTE activity
  3. Outer query
  */

In event modeling, engagement segmentation by behavior frequency is a foundational analysis. The standard two-step pattern is to aggregate each user’s event count in a CTE, then classify segments with an outer CASE WHEN.

CASE
  WHEN event_count >= 5 THEN 'power'
  WHEN event_count >= 2 THEN 'casual'
  ELSE                       'light'
END
-- WHEN clauses are evaluated from top to bottom; the first true THEN is used

From the user_events table, aggregate each user’s event count and classify users into three segments: power (5 or more), casual (2–4), and light (1). Return the output columns segment, user_count, avg_events (avg_events rounded to one decimal place), ordered by user_count descending and segment name ascending.

Note: user1=5 events → power, user2=3 → casual, user3=2 → casual, user4=1 → light.

Expected output (user_count descending / segment ascending):

casual is largest (average 2.5 from user2=3 and user3=2). light and power each have one user, so segment name breaks the tie (l < p).

WITH event_counts AS (                   -- Per-user event counts
  SELECT
    user_id,
    COUNT(*) AS event_count
  FROM   user_events
  GROUP BY user_id
),
segmented AS (                         -- Add a segment column with CASE WHEN
  SELECT
    user_id,
    event_count,
    CASE
      WHEN event_count >= 5 THEN 'power'   -- evaluated first (5 or more)
      WHEN event_count >= 2 THEN 'casual'  -- evaluated next (at least 2 and less than 5)
      ELSE                       'light'   -- otherwise (1)
    END AS segment
  FROM   event_counts
)
SELECT
  segment,
  COUNT(*)                   AS user_count,
  ROUND(AVG(event_count), 1) AS avg_events  -- Average event count within the segment
FROM   segmented
GROUP BY segment
ORDER BY user_count DESC, segment;   -- user_count descending; segment ascending for ties

/*
  Logical execution order:
  1. CTE event_counts
  2. CTE segmented
  3. Outer query
  */

Dormant-user detection is a key event-modeling task. To find users whose last event was at least N days ago or who have never had an event, the basic pattern is LEFT JOIN + GROUP BY + HAVING.

FROM       users u
LEFT JOIN  user_events e ON u.user_id = e.user_id
GROUP BY   u.user_id, u.signup_date
HAVING     MAX(e.event_time) < '2024-01-25'   -- at least the required number of days before the reference date
        OR MAX(e.event_time) IS NULL           -- no event ever

From the users and user_events tables, users whose last event was before 2024-01-25 (at least 21 days before the reference date 2024-02-15), or who have never had an event. Return user_id, signup_date, last_event_time, ordered by last_event_time ascending with NULLS FIRST, then user_id ascending.

Note: user1 last=1/20 (26 days ago → dormant), user2 last=1/10 (36 days ago → dormant), user3 last=2/10 (active), user4 last=1/22 (24 days ago → dormant), user5=no events

Expected output (last_event_time NULLS FIRST / user_id ascending):

user3’s last event (2/10) was 5 days before the reference date (2/15), so it is active and excluded.

SELECT
  u.user_id,
  u.signup_date,
  MAX(e.event_time) AS last_event_time       -- last event timestamp (NULL when there are no events)
FROM       users u
LEFT JOIN  user_events e ON u.user_id = e.user_id  -- retain users with no events
GROUP BY   u.user_id, u.signup_date
HAVING     MAX(e.event_time) < '2024-01-25'::date  -- last event was at least 21 days ago
        OR MAX(e.event_time) IS NULL               -- no event ever
ORDER BY   last_event_time ASC NULLS FIRST,        -- put NULL (no event) first
           u.user_id;

/*
  Logical execution order:
  1. FROM users LEFT JOIN user_events          → retain all users
  2. GROUP BY u.user_id, u.signup_date         → aggregate and get MAX(event_time)
  3. HAVING ...                                → select last events before the reference date or NULL
  4. ORDER BY last_event_time ASC NULLS FIRST  → sort and output
  */