DoorDash Key Metrics & Dashboard Requirements Questions

-- Assumes users(user_id, first_seen, observation_end) and events(user_id, event_date)
WITH cohorts AS (
  SELECT user_id,
         date_trunc('month', first_seen)::date AS cohort_month,
         observation_end::date AS observation_end
  FROM users
),
events_month AS (
  SELECT e.user_id,
         date_trunc('month', e.event_date)::date AS event_month
  FROM events e
  JOIN cohorts c USING (user_id)
  WHERE e.event_date >= c.cohort_month  -- optional
  GROUP BY 1,2
),
activity AS (
  SELECT c.cohort_month,
         em.event_month,
         EXTRACT(YEAR FROM age(em.event_month, c.cohort_month)) * 12
           + EXTRACT(MONTH FROM age(em.event_month, c.cohort_month)) AS month_n,
         em.user_id
  FROM events_month em
  JOIN cohorts c USING (user_id)
),
cohort_sizes AS (
  SELECT cohort_month,
         COUNT(*) AS cohort_size
  FROM cohorts
  GROUP BY cohort_month
),
at_risk AS (
  -- for each cohort_month and month_n, count users whose observation_end >= cohort_month + month_n
  SELECT c.cohort_month,
         generate_series(0, 24) AS month_n  -- N window (adjust)
  FROM (SELECT DISTINCT cohort_month FROM cohorts) c
),
denom AS (
  SELECT a.cohort_month, a.month_n, COUNT(DISTINCT c.user_id) AS at_risk_users
  FROM cohorts c
  JOIN at_risk a ON a.cohort_month = c.cohort_month
  WHERE c.observation_end >= (a.cohort_month + (a.month_n || ' months')::interval)::date
  GROUP BY 1,2
),
num AS (
  SELECT cohort_month, month_n, COUNT(DISTINCT user_id) AS active_users
  FROM activity
  WHERE month_n >= 0
  GROUP BY 1,2
)
SELECT d.cohort_month,
       d.month_n,
       COALESCE(n.active_users,0) AS active_users,
       (SELECT cohort_size FROM cohort_sizes cs WHERE cs.cohort_month = d.cohort_month) AS cohort_size,
       CASE WHEN d.at_risk_users = 0 THEN NULL
            ELSE ROUND( (COALESCE(n.active_users,0)::numeric / d.at_risk_users)*100, 4)
       END AS retention_rate_percent
FROM denom d
LEFT JOIN num n USING (cohort_month, month_n)
ORDER BY cohort_month, month_n;

# users: user_id, first_seen (datetime), observation_end (datetime)
# events: user_id, event_date (datetime)
users['cohort_month'] = users['first_seen'].dt.to_period('M').dt.to_timestamp()
events['event_month'] = events['event_date'].dt.to_period('M').dt.to_timestamp()

# unique monthly activity
activity = events.drop_duplicates(['user_id','event_month']).merge(users[['user_id','cohort_month','observation_end']], on='user_id')
activity['month_n'] = ((activity['event_month'].dt.year - activity['cohort_month'].dt.year)*12 +
                       (activity['event_month'].dt.month - activity['cohort_month'].dt.month))

# denom: for each user, compute max_month_n_observable = floor(months between cohort_month and observation_end)
users['max_n'] = ((users['observation_end'].dt.year - users['cohort_month'].dt.year)*12 +
                  (users['observation_end'].dt.month - users['cohort_month'].dt.month))
# expand into rows per month_n up to max_n (use explode or efficient bin-aggregation)
# then groupby cohort_month, month_n to get at_risk_users; group activity to get active_users