cohort.sql

-- COHORT ANALYSIS
-- (merchant_location_id, cohort_month), each
with cohort_items as (
  select
    date_trunc('month', ML.created_at)::date as cohort_month,
    id as merchant_location_id
  from merchant_locations ML
),
-- (merchant_location_id, month_number): merchant X has activity in month number X
merchant_appointments as (
  select
    A.merchant_location_id,
    (DATE_PART('year', A.created_at::date) - DATE_PART('year', cohort_month::date)) * 12 +
    (DATE_PART('month', A.created_at::date) - DATE_PART('month', cohort_month::date)) as month_number,
    count(A) as appt_count
  from appointments A
  left join cohort_items C ON A.merchant_location_id = C.merchant_location_id
  group by 1, 2
),
-- (cohort_month, size)
cohort_size as (
  select cohort_month, count(1) as num_merchants
  from cohort_items
  group by 1
),
-- (cohort_month, month_number, cnt)
B as (
  select
    C.cohort_month,
    A.month_number,
    count(1) as num_merchants,
    sum(A.appt_count) as appt_count
  from merchant_appointments A
  left join cohort_items C ON A.merchant_location_id = C.merchant_location_id
  group by 1, 2
)
-- our final value: (cohort_month, size, month_number, percentage)
select
  B.cohort_month,
  S.num_merchants as total_merchants,
  B.month_number,
  B.num_merchants::float * 100 / S.num_merchants as percentage,
  B.appt_count
from B
left join cohort_size S ON B.cohort_month = S.cohort_month
where B.cohort_month IS NOT NULL
order by 1, 3;