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…