SQL KPI Analysis — Applied Funnels, Cohorts, Pareto

WITH step_counts AS (
  SELECT
    step,
    CASE step                       -- define funnel order numerically
      WHEN 'visit'    THEN 1
      WHEN 'signup'   THEN 2
      WHEN 'activate' THEN 3
      WHEN 'purchase' THEN 4
    END AS step_order,
    COUNT(DISTINCT user_id) AS users
  FROM  funnel_events
  GROUP BY step
)
SELECT
  step, users,
  LAG(users) OVER (ORDER BY step_order) AS prev_users,
  ROUND(
    users * 100.0
    / NULLIF(LAG(users) OVER (ORDER BY step_order), 0), 2
  ) AS step_cvr
FROM  step_counts
ORDER BY step_order;

/*
  Execution order:
  1. FROM funnel_events                       → read the rows
  2. GROUP BY step + COUNT(DISTINCT user_id)  → aggregate unique users by step
  3. WITH step_counts                         → complete the CTE
  4. LAG(users) OVER (ORDER BY step_order)    → retrieve the previous-step users
  5. Calculate step-to-step conversion       → divide by the previous step
  */

WITH RECURSIVE date_spine AS (
  SELECT DATE '2024-02-01' AS d        -- ① anchor (1 row)
  UNION ALL
  SELECT d + 1                          -- ② recursive term: previous d + 1
  FROM  date_spine
  WHERE d < DATE '2024-02-05'          -- ③ stop condition
)
SELECT
  s.d AS activity_date,
  COALESCE(a.active_users, 0) AS active_users  -- fill unmatched dates with 0
FROM      date_spine s
LEFT JOIN active_days a ON a.activity_date = s.d
ORDER BY s.d;

/*
  Execution order (recursive CTE expansion):
  1. Anchor                              → generate the starting date
  2. Recursion                           → add the next date one day at a time
  3. Recursion ends                      → stop at the upper bound
  4. date_spine LEFT JOIN active_days    → attach activity (missing values are NULL)
  5. COALESCE(NULL,0) / ORDER BY s.d     → fill with 0 and output in date order
  */

WITH first_login AS (              -- ① cohort date = first login date
  SELECT user_id, MIN(login_date) AS cohort_date
  FROM  logins
  GROUP BY user_id
),
activity AS (                          -- ② calculate elapsed week
  SELECT
    f.cohort_date, f.user_id,
    (l.login_date - f.cohort_date) / 7 AS week_no
  FROM      first_login f
  JOIN      logins l USING (user_id)
)
SELECT
  cohort_date,
  COUNT(DISTINCT user_id) AS cohort_size,
  ROUND(COUNT(DISTINCT user_id) FILTER (WHERE week_no = 0)
      * 100.0 / COUNT(DISTINCT user_id), 2) AS w0_pct,
  ROUND(COUNT(DISTINCT user_id) FILTER (WHERE week_no = 1)
      * 100.0 / COUNT(DISTINCT user_id), 2) AS w1_pct,
  ROUND(COUNT(DISTINCT user_id) FILTER (WHERE week_no = 2)
      * 100.0 / COUNT(DISTINCT user_id), 2) AS w2_pct
FROM  activity
GROUP BY cohort_date
ORDER BY cohort_date;

/*
  Execution order:
  1. first_login                     → aggregate each user's cohort date
  2. activity                        → calculate the week number
  3. GROUP BY cohort_date            → aggregate by cohort
  4. COUNT(DISTINCT user_id) FILTER  → calculate retention by week
  */

WITH ranked AS (
  SELECT
    customer_id, revenue,
    RANK() OVER (ORDER BY revenue DESC) AS rev_rank,
    ROUND(revenue * 100.0 / SUM(revenue) OVER (), 2) AS rev_share,  -- revenue share
    ROUND(
      SUM(revenue) OVER (ORDER BY revenue DESC
                         ROWS UNBOUNDED PRECEDING)            -- cumulative from the top
      * 100.0 / SUM(revenue) OVER (), 2
    ) AS cum_share
  FROM  customer_revenue
)
SELECT
  customer_id, revenue, rev_rank, rev_share, cum_share,
  CASE                                  -- classify by cumulative share
    WHEN cum_share <= 80  THEN 'A'
    WHEN cum_share <= 95  THEN 'B'
    ELSE 'C'
  END AS abc_class
FROM  ranked
ORDER BY rev_rank;

/*
  Execution order:
  1. FROM customer_revenue                          → read the rows
  2. SUM(revenue) OVER ()                           → attach the grand total to every row
  3. SUM(revenue) OVER (ORDER BY revenue DESC ...)  → calculate the running total from the top
  4. rev_share / cum_share                          → calculate the shares
  5. Outer CASE                                     → classify rows as A, B, or C
  */