BI Analyst Interview Case Studies & Drills

-- SaaS Cohort Retention Rate SQL Sample
WITH user_first_order AS (
  SELECT user_id, MIN(DATE_TRUNC('month', order_date)) AS cohort_month
  FROM orders GROUP BY 1
),
activity AS (
  SELECT u.cohort_month,
         DATE_PART('month', AGE(DATE_TRUNC('month', o.order_date), u.cohort_month)) AS period_month,
         COUNT(DISTINCT o.user_id) AS active_users
  FROM user_first_order u
  JOIN orders o ON u.user_id = o.user_id
  GROUP BY 1, 2
)
SELECT * FROM activity ORDER BY cohort_month, period_month;