دليل الأداة

استعلام جدول احتفاظ الأفواج كما نُفِّذ على قاعدة البيانات.

WITH first_tx AS (
  SELECT user_id, date_trunc('month', MIN(tx_at)) AS cohort
  FROM transactions WHERE status = 'success'
  GROUP BY user_id
), activity AS (
  SELECT DISTINCT f.cohort, t.user_id,
         date_trunc('month', t.tx_at) AS month
  FROM transactions t JOIN first_tx f USING (user_id)
  WHERE t.status = 'success'
), counted AS (
  SELECT cohort, COUNT(*) AS active,
         (EXTRACT(year  FROM age(month, cohort)) * 12 +
          EXTRACT(month FROM age(month, cohort)))::int AS n
  FROM activity GROUP BY cohort, n
)
SELECT to_char(cohort, 'YYYY-MM') AS cohort, n AS month_n,
       active,
       ROUND(active::numeric / FIRST_VALUE(active) OVER (
         PARTITION BY cohort ORDER BY n), 4) AS retention
FROM counted ORDER BY cohort, n;