Analytics made understandable with business-first examples you can share.

WITH first_orders AS (
  SELECT user_id, MIN(order_date)::date AS cohort_date
  FROM orders GROUP BY 1
),
orders_by_week AS (
  SELECT o.user_id,
         DATE_TRUNC('week', o.order_date)::date AS order_week,
         f.cohort_date
  FROM orders o
  JOIN first_orders f USING (user_id)
)
SELECT cohort_date,
       order_week,
       COUNT(DISTINCT user_id) AS active_users,
       ROUND(100.0 * COUNT(DISTINCT user_id)
         / NULLIF(MAX(COUNT(DISTINCT user_id)) OVER (PARTITION BY cohort_date),0), 1) AS retention_pct
FROM orders_by_week
GROUP BY cohort_date, order_week
ORDER BY cohort_date, order_week;