Date Operations & Time-Based Analytics SQL Topic exerciseHardVerified answerSQLite + PostgreSQL + MySQL + SQL Server live · 1 guided

First-Purchase Cohort Retention by Month

For each first-purchase cohort, report active customers, cohort size, and retention by months since first purchase.

  • Joins
  • Subqueries
  • Aggregation
  • Date analysis
  • Numeric functions

Exercise brief

Understand the request

Customer analytics director Lifecycle reporting defines acquisition by a customer’s first observed purchase month.

Lifecycle reporting defines acquisition by a customer’s first observed purchase month. For each first-purchase cohort, report active customers, cohort size, and retention by months since first purchase.

Return

  • Return cohort_month, months_since_signup, active_customers, cohort_size, retention_rate in this exact left-to-right order.

Constraints

  • Define cohort_month from MIN(order_date), not customers.signup_date.
  • Count distinct customers at each cohort-age grain.
  • Order by cohort_month and months_since_first_purchase.

Data you will use

Review the relevant tables before deciding how to join, filter, or aggregate them.

orders

  • order_idINTEGER
  • customer_idINTEGER
  • order_dateDATE

Hints, when you need them

Open one clue at a time so you still do the reasoning.

Hint 1

Cohort = the month of each customer’s FIRST order (MIN(order_date), start of month).

Hint 2

months_since_signup = (act_year − cohort_year)*12 + (act_month − cohort_month) — exact, not days/30.

Hint 3

retention_rate = active_customers / cohort_size × 100.

Verified SQL answer

Attempt the problem first, then compare structure and reasoning—not just syntax.

Reveal solution and explanation
WITH cohort_data AS (SELECT customer_id, date(MIN(order_date), 'start of month') AS cohort_month FROM orders GROUP BY customer_id), monthly_activity AS (SELECT cd.cohort_month, cd.customer_id, date(o.order_date, 'start of month') AS activity_month FROM cohort_data cd JOIN orders o ON cd.customer_id = o.customer_id) SELECT cohort_month, (CAST(strftime('%Y', activity_month) AS INTEGER) - CAST(strftime('%Y', cohort_month) AS INTEGER)) * 12 + (CAST(strftime('%m', activity_month) AS INTEGER) - CAST(strftime('%m', cohort_month) AS INTEGER)) AS months_since_signup, COUNT(DISTINCT customer_id) AS active_customers, (SELECT COUNT(DISTINCT customer_id) FROM cohort_data WHERE cohort_month = ma.cohort_month) AS cohort_size, ROUND((COUNT(DISTINCT customer_id) * 100.0) / (SELECT COUNT(DISTINCT customer_id) FROM cohort_data WHERE cohort_month = ma.cohort_month), 2) AS retention_rate FROM monthly_activity ma GROUP BY ma.cohort_month, months_since_signup ORDER BY cohort_month, months_since_signup;

Why this works

Cohort retention is the canonical growth-analytics report. The Tier-1 fix replaces the fragile julianday/30 month estimate with exact (Y*12+M) arithmetic, which never miscounts across long gaps or leap years. Truncating dates to month-start uses date(d,'start of month') in SQLite, DATE_TRUNC('month', d) in Postgres, and DATEFROMPARTS/EOMONTH tricks in SQL Server.

Success check

The result is a long-form retention grid with month zero equal to 100 percent for every cohort.

Expected result

Use this output to verify values, aliases, ordering, and row count.

cohort_monthmonths_since_signupactive_customerscohort_sizeretention_rate
2023-01-01011100
2023-01-011311100
2023-01-011611100
2023-01-011911100
2023-02-01011100
2023-02-011111100
2023-02-011311100
2023-02-011511100
2023-02-011911100
2024-01-01011100

Previewing 10 of 20 expected rows. Run the query in the editor to inspect the full result.

Learn the concepts behind this answer

Strengthen your understanding with these targeted learning topics:

Continue practicing

Build the next SQL skill

SQL Practice Online →

Open the interactive workspace and practice across SQL topics.