Customer Value by First-Purchase Cohort
Calculate customer count, total revenue, and average realized revenue per customer for each first-purchase cohort.
- Joins
- Subqueries
- Aggregation
- Date analysis
- Numeric functions
Exercise brief
Understand the request
Marketing analytics lead Acquisition reporting compares realized order revenue across first-purchase cohorts.
Acquisition reporting compares realized order revenue across first-purchase cohorts. Calculate customer count, total revenue, and average realized revenue per customer for each first-purchase cohort.
Return
- Return cohort_month, number_of_customers, total_cohort_revenue, average_clv in this exact left-to-right order.
Constraints
- Define the cohort from each customer’s first observed order.
- Count distinct customers after joining all cohort orders.
- Order by cohort_month.
Data you will use
Review the relevant tables before deciding how to join, filter, or aggregate them.
orders
order_idINTEGERcustomer_idINTEGERorder_dateDATEorder_totalDECIMAL
Hints, when you need them
Open one clue at a time so you still do the reasoning.
Hint 1
First assign each customer to a cohort_month (their first-order month).
Hint 2
Join back to all their orders and SUM revenue per cohort.
Hint 3
average_clv = total_cohort_revenue / distinct customers in the cohort.
Verified SQL answer
Attempt the problem first, then compare structure and reasoning—not just syntax.
Reveal solution and explanation
WITH customer_cohorts AS (SELECT customer_id, date(MIN(order_date), 'start of month') AS cohort_month FROM orders GROUP BY customer_id) SELECT cc.cohort_month, COUNT(DISTINCT cc.customer_id) AS number_of_customers, SUM(o.order_total) AS total_cohort_revenue, ROUND(SUM(o.order_total) / COUNT(DISTINCT cc.customer_id), 2) AS average_clv FROM customer_cohorts cc JOIN orders o ON cc.customer_id = o.customer_id GROUP BY cc.cohort_month ORDER BY cc.cohort_month;Why this works
CLV-by-cohort answers 'which acquisition months produced the most valuable customers?'. The pattern is two-stage: derive each customer's cohort, then aggregate their lifetime revenue grouped by cohort. Only the month-truncation function changes per engine.
Success check
Each cohort appears once and average_clv uses total cohort revenue divided by distinct cohort customers.
Expected result
Use this output to verify values, aliases, ordering, and row count.
| cohort_month | number_of_customers | total_cohort_revenue | average_clv |
|---|---|---|---|
| 2023-01-01 | 1 | 840 | 840 |
| 2023-02-01 | 1 | 1770 | 1770 |
| 2024-01-01 | 1 | 1560 | 1560 |
| 2024-03-01 | 1 | 880 | 880 |
| 2024-04-01 | 1 | 940 | 940 |
Learn the concepts behind this answer
Strengthen your understanding with these targeted learning topics:
Continue practicing
Build the next SQL skill
WHERE Clause & Filtering
Practice SQL WHERE clauses with realistic boundary, NULL, text, date, exclusion, and production-filtering problems.
SQL Aggregations
Build reliable SQL metrics from aggregate functions through grain, fan-out, weighted ratios, rollups, percentiles, and approximate counts.
CTEs & Window Functions
Practice modular CTE pipelines, deterministic window analytics, period comparisons, deduplication, frames, and gaps-and-islands.
Open the interactive workspace and practice across SQL topics.