Drill 12 of 12 in Marketing questions
0

Interview Question Bank · Running SUM per cohort · first crossing · open cohorts

12. CAC payback by cohort

HardMarketingQuestion #180

Concept: Running SUM per cohort · first crossing · open cohortsshow

Payback asks when cumulative revenue first covers the acquisition cost. Build the running total with SUM(revenue) OVER (PARTITION BY cohort ORDER BY month), then take the smallest month where it reaches the cost: MIN(month) FILTER (WHERE running >= cost). Cohorts that haven't paid back must still appear with NULL, so start from the cohort table and LEFT JOIN, rather than filtering them away in WHERE.

Example (different data):

WITH run AS (
  SELECT machine_id, week,
    SUM(output_value) OVER (PARTITION BY machine_id ORDER BY week) AS cum_value
  FROM machine_output
)
SELECT m.id, MIN(r.week) FILTER (WHERE r.cum_value >= m.price) AS payback_week
FROM machines m LEFT JOIN run r ON r.machine_id = m.id
GROUP BY m.id;

Your task

Larkspur Roasters' finance team wants to know how long each monthly acquisition cohort takes to earn back what it cost to acquire. Revenue is tracked through March 2025, so recent cohorts may not have paid back yet.

For every cohort in ad_cohorts, return cohort (YYYY-MM), new_customers, cac (acquisition_spend / new_customers, rounded to 2 decimals) and payback_months (the first month_number at which the cohort's cumulative revenue is at least its acquisition_spend, NULL if it hasn't happened yet). Sort by cohort.

Starting Postgres in your browser…

Checking your access…