Donor Retention by First-Gift Year

Hard
95.

Donor Retention by First-Gift Year

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

ColumnType
contribution_idint
donor_idint
amountint
contributed_ondate

Primary key: contribution_id.

One row per gift received, in rupees. The NGO's records start in 2023.

Example

The rows must come back in the order shown.

contributions

contribution_iddonor_idamountcontributed_on
1110002023-03-14
2115002024-03-14
325002023-08-01
425002023-11-01
528002025-01-10
6320002023-12-31
7320002024-01-01
8412002024-05-05
9412002025-05-05
10530002024-09-09
1 more row

Output

cohort_yeardonorsretained_next_yearretention_pct
20233266.67
20242150
2025100
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
contribution_idint
donor_idint
amountint
contributed_ondate

Primary key: contribution_id.

One row per gift received, in rupees. The NGO's records start in 2023.

Examples

Example 1

contributions

contribution_iddonor_idamountcontributed_on
1110002023-03-14
2115002024-03-14
325002023-08-01
425002023-11-01
528002025-01-10
6320002023-12-31
7320002024-01-01
8412002024-05-05
9412002025-05-05
10530002024-09-09
1167002025-02-02

Output

cohort_yeardonorsretained_next_yearretention_pct
20233266.67
20242150
2025100

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_year

Another 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 →