Donor Retention by First-Gift Year — SQL Aggregation Problem
An NGO tracks donor retention by cohort: a donor's cohort is the calendar year of their first ever gift.
- Difficulty: Hard
- Topics: Aggregation, Subqueries, Dates
- Dialect: MySQL
- Problem: #95
Problem statement
An NGO tracks donor retention by cohort: a donor's cohort is the calendar year of their first ever gift. A donor is retained when they also gave at least once in the calendar year right after their cohort year.
For every cohort year, return cohort_year, donors (donors in the cohort), retained_next_year and retention_pct = retained_next_year * 100 / donors rounded to 2 decimals, ordered by cohort_year. A gift two years later does not count as retention.
Tables
Table: contributions
| Column | Type |
|---|---|
| contribution_id | int |
| donor_id | int |
| amount | int |
| contributed_on | date |
Primary key: contribution_id.
One row per gift received, in rupees. The NGO's records start in 2023.
Examples
Example 1
contributions
| contribution_id | donor_id | amount | contributed_on |
|---|---|---|---|
| 1 | 1 | 1000 | 2023-03-14 |
| 2 | 1 | 1500 | 2024-03-14 |
| 3 | 2 | 500 | 2023-08-01 |
| 4 | 2 | 500 | 2023-11-01 |
| 5 | 2 | 800 | 2025-01-10 |
| 6 | 3 | 2000 | 2023-12-31 |
| 7 | 3 | 2000 | 2024-01-01 |
| 8 | 4 | 1200 | 2024-05-05 |
| 9 | 4 | 1200 | 2025-05-05 |
| 10 | 5 | 3000 | 2024-09-09 |
| 11 | 6 | 700 | 2025-02-02 |
Output
| cohort_year | donors | retained_next_year | retention_pct |
|---|---|---|---|
| 2023 | 3 | 2 | 66.67 |
| 2024 | 2 | 1 | 50 |
| 2025 | 1 | 0 | 0 |
How to solve Donor Retention by First-Gift Year
A cohort report has two steps: assign every donor to a cohort, then measure each cohort. Reducing the gifts to distinct (donor_id, year) pairs first keeps both steps simple — a donor's cohort is MIN(yr), and "gave again next year" becomes the existence of the pair (donor_id, cohort_year + 1).
The LEFT JOIN on that pair keeps every cohort member and matches at most one row (the pairs are distinct), so COUNT(n.donor_id) is the retained count and COUNT(*) the cohort size; the percentage is their ratio, rounded to two places. In the example the 2023 cohort has donors 1, 2 and 3: donor 1 returned in 2024, donor 3 on 1 January 2024 (a gift a day later still counts, because it is a new calendar year), and donor 2 skipped 2024 and came back only in 2025 — not retained.
The alternative finds the cohort with YEAR(MIN(contributed_on)) and tests retention with a correlated EXISTS, folded into a 0/1 flag. Both are a grouping pass plus one lookup per donor. The newest cohort has no following year yet, so its retention is 0 — a real report would flag it as incomplete.
Reference solution (MySQL)
WITH donor_years AS (
SELECT DISTINCT donor_id, YEAR(contributed_on) AS yr FROM contributions
),
cohorts AS (
SELECT donor_id, MIN(yr) AS cohort_year FROM donor_years GROUP BY donor_id
)
SELECT c.cohort_year,
COUNT(*) AS donors,
COUNT(n.donor_id) AS retained_next_year,
ROUND(COUNT(n.donor_id) * 100 / COUNT(*), 2) AS retention_pct
FROM cohorts c
LEFT JOIN donor_years n ON n.donor_id = c.donor_id AND n.yr = c.cohort_year + 1
GROUP BY c.cohort_year
ORDER BY c.cohort_yearAnother way
SELECT cohort_year, COUNT(*) AS donors, SUM(retained) AS retained_next_year, ROUND(SUM(retained) * 100 / COUNT(*), 2) AS retention_pct
FROM (
SELECT f.donor_id, f.cohort_year,
CASE WHEN EXISTS (SELECT 1 FROM contributions g WHERE g.donor_id = f.donor_id AND YEAR(g.contributed_on) = f.cohort_year + 1) THEN 1 ELSE 0 END AS retained
FROM (SELECT donor_id, YEAR(MIN(contributed_on)) AS cohort_year FROM contributions GROUP BY donor_id) f
) t
GROUP BY cohort_year
ORDER BY cohort_year← Median Days to Close Service Requests by Department · Blocks of Three or More Free Adjacent Concert Seats →