Structured Problem Solving and Frameworks Questions

WITH cohorts AS (
  SELECT
    id AS user_id,
    date_trunc('day', created_at) :: date AS cohort_start_date
  FROM users
),
events_open AS (
  SELECT
    e.user_id,
    date_trunc('day', u.created_at) :: date AS cohort_start_date,
    e.occurred_at::timestamp,
    (e.occurred_at::date - u.created_at::date) AS days_after_signup
  FROM events e
  JOIN users u ON u.id = e.user_id
  WHERE e.event_type = 'open_app'
)
SELECT
  c.cohort_start_date,
  COUNT(DISTINCT c.user_id) AS cohort_size,
  -- users with at least one open_app where days_after_signup between 1 and 7
  COUNT(DISTINCT CASE WHEN eo.days_after_signup BETWEEN 1 AND 7 THEN c.user_id END) AS retained_day_7_users,
  ROUND(
    100.0 * COUNT(DISTINCT CASE WHEN eo.days_after_signup BETWEEN 1 AND 7 THEN c.user_id END)
    / NULLIF(COUNT(DISTINCT c.user_id), 0)
    , 2
  ) AS retained_day_7_rate
FROM cohorts c
LEFT JOIN events_open eo
  ON eo.user_id = c.user_id
  AND eo.cohort_start_date = c.cohort_start_date
GROUP BY c.cohort_start_date
ORDER BY c.cohort_start_date;