Retention analysis measures whether users who registered in a given month continue using the service in later months. The number of users active in the month after the cohort month divided by the cohort size is the Month 1 retention rate.

Using the users and login_events tables, calculate the next-month retention rate for each registration-month cohort. Return cohort_month, cohort_size, retained_month1, retention_rate_pct (rounded to one decimal place), ordered by cohort_month ascending.

Expected output (cohort_month ascending):

January cohort (users 1, 2, and 3) → 2 users, users 1 and 3, logged in during the following February = 66.7%. February cohort (users 4, 5, and 6) → only user4 logged in during the following March = 33.3%.

WITH cohorts AS (
  -- Round each registration date to the first of its month and define the cohort
  SELECT
    user_id,
    DATE_TRUNC('month', registered_at)::date  AS cohort_month
  FROM  users
),
activity AS (
  -- Get the distinct list of months in which each user was active
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_date)::date    AS active_month
  FROM  login_events
)
SELECT
  c.cohort_month,
  COUNT(DISTINCT c.user_id)  AS cohort_size,
  COUNT(DISTINCT a.user_id)  AS retained_month1,
  ROUND(
    COUNT(DISTINCT a.user_id) * 100.0 /
    COUNT(DISTINCT c.user_id), 1
  ) AS retention_rate_pct
FROM   cohorts c
LEFT JOIN activity a
  ON  a.user_id     = c.user_id
  AND a.active_month = c.cohort_month + INTERVAL '1 month'  -- Join only the following month
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

/*
  Logical execution order:
  1. CTE cohorts            → Define the CTE
  2. CTE activity           → Define the CTE
  3. FROM cohorts c
     LEFT JOIN activity a   → Join while retaining every left-side row
  4. GROUP BY cohort_month  → Form groups
  5. Evaluate aggregates     → COUNT(DISTINCT ...)
  6. SELECT                 → Format values
*/

Funnel analysis quantifies how users progress through multiple steps such as “view → signup → purchase” toward a goal. PostgreSQL's FILTER (WHERE ...) clause aggregates counts for each step more concisely than CASE WHEN.

-- FILTER clause (recommended)
COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'signup')

-- CASE WHEN (equivalent but more verbose)
COUNT(DISTINCT CASE WHEN event_type = 'signup' THEN user_id END)

Using the funnel_events table, calculate the number of users reaching each step and the conversion rates for a four-step funnel (page view → signup → add to cart → purchase). Aggregate each step in a CTE and calculate CVR in the outer query. Return page_view, signup, add_cart, purchase, view_to_signup_pct, signup_to_cart_pct, cart_to_purchase_pct (each percentage rounded to one decimal place).

Expected output (one row):

page_view (5) → signup (4): 80% / signup (4) → add_cart (3): 75% / add_cart (3) → purchase (2): 66.7% — quantify the drop-off at each step.

WITH counts AS (
  -- Consolidate the users reaching each step into one row
  SELECT
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'page_view') AS s1,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'signup')    AS s2,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'add_cart')  AS s3,
    COUNT(DISTINCT user_id) FILTER (WHERE event_type = 'purchase')  AS s4
  FROM   funnel_events
)
SELECT
  s1 AS page_view,
  s2 AS signup,
  s3 AS add_cart,
  s4 AS purchase,
  ROUND(s2 * 100.0 / s1, 1) AS view_to_signup_pct,    -- step 1→2 conversion rate
  ROUND(s3 * 100.0 / s2, 1) AS signup_to_cart_pct,    -- step 2→3 conversion rate
  ROUND(s4 * 100.0 / s3, 1) AS cart_to_purchase_pct   -- step 3→4 conversion rate
FROM   counts;

/*
  Logical execution order:
  1. CTE counts        → Define the CTE
  2. Evaluate aggregates → Aggregate with COUNT(DISTINCT ...) FILTER
  3. Evaluate outer query → Format values and select columns
*/

Stickiness is calculated as average DAU (daily active users) ÷ MAU (monthly active users). It indicates how frequently users use the service.

Using the user_sessions table, calculate monthly avg_dau, MAU, and stickiness. Return month, avg_dau, mau, stickiness_pct (each value rounded to one decimal place), ordered by month ascending.

Expected output (month ascending):

January: avg_dau=(3+2+1)/3=2.0, MAU=4 → stickiness=50%. February: avg_dau=(2+1+1)/3≈1.3, MAU=3 → stickiness=43.3%.

WITH daily AS (
  -- Aggregate daily DAU (unique users)
  SELECT
    DATE_TRUNC('month', session_date)::date  AS month,
    session_date,
    COUNT(DISTINCT user_id)               AS dau
  FROM   user_sessions
  GROUP BY month, session_date
),
monthly AS (
  -- Aggregate monthly MAU (unique users within the month)
  SELECT
    DATE_TRUNC('month', session_date)::date  AS month,
    COUNT(DISTINCT user_id)               AS mau
  FROM   user_sessions
  GROUP BY month
)
SELECT
  m.month,
  ROUND(AVG(d.dau), 1)                        AS avg_dau,
  m.mau,
  ROUND(AVG(d.dau) * 100.0 / m.mau, 1)       AS stickiness_pct  -- average DAU / MAU
FROM   monthly m
JOIN   daily   d USING (month)
GROUP BY m.month, m.mau
ORDER BY m.month;

/*
  Logical execution order:
  1. CTE daily            → Define the CTE
  2. CTE monthly          → Define the CTE
  3. FROM monthly m
     JOIN daily d         → Join matching rows only
  4. GROUP BY m.month     → Form groups
  5. Evaluate aggregates  → AVG(d.dau)
  6. SELECT               → Format values
  7. ORDER BY m.month     → Sort and output
*/

Churn analysis identifies users who were active in the previous month but are not active in the current month. In SQL, the LEFT JOIN IS NULL pattern (an anti-join) is both readable and efficient.

Using the login_events table, identify the users who were active in January 2024 but did not log in during February 2024 (churned). Return user_id, active_month, is_churned, ordered by user_id ascending.

Expected output (user_id ascending):

user2 was active in January but did not log in during February, so the user churned. users 1 and 3 continued into February. user4 was not in January and is outside the target population.

WITH jan_active AS (
  -- Extract users active in January 2024 (deduplicate them)
  SELECT DISTINCT user_id
  FROM   login_events
  WHERE  DATE_TRUNC('month', event_date) = '2024-01-01'
),
feb_active AS (
  -- Extract users active in February 2024 (deduplicate them)
  SELECT DISTINCT user_id
  FROM   login_events
  WHERE  DATE_TRUNC('month', event_date) = '2024-02-01'
)
SELECT
  j.user_id,
  '2024-01'                                             AS active_month,
  CASE WHEN f.user_id IS NULL THEN true ELSE false END  AS is_churned  -- NULL = no next-month login = churn
FROM   jan_active  j
LEFT JOIN feb_active f USING (user_id)   -- A user absent in February has f.user_id = NULL
ORDER BY j.user_id;

/*
  Logical execution order:
  1. CTE jan_active        → Define the CTE
  2. CTE feb_active        → Define the CTE
  3. FROM jan_active j
     LEFT JOIN feb_active  → Join while retaining every left-side row
  4. SELECT                → Evaluate columns (classify with CASE)
  5. ORDER BY j.user_id    → Sort and output
*/