bassemawhoob

-- Based on: https://medium.com/quick-code/how-to-write-sql-to-calculate-user-cohort-retention-a1b3b57c7a2f
-- Uses DATE_PART instead of DATE_DIFF as it is not supported by PostgreSQL

-- (branch_id, scheduled_for, total_cents)
WITH activities AS (
  SELECT branch_id, scheduled_for, total_cents
  FROM orders
  WHERE orders.status = 'complete'
),
-- (branch_id, cohort_month): cohort month is the first order date