Recurring Donors Across Three Months of 2024 — SQL Problem
An NGO calls a donor recurring if they gave in at least three different calendar months of 2024. Several gifts in the same month count as one month.
- Difficulty: Medium
- Topics: Dates, Aggregation
- Dialect: MySQL
- Problem: #84
Problem statement
An NGO calls a donor recurring if they gave in at least three different calendar months of 2024. Several gifts in the same month count as one month.
Return each recurring donor with donor_id, months_given (distinct months of 2024 with a gift) and total_2024 (the sum of their 2024 gifts, in rupees). Gifts outside 2024 are ignored. Order by months_given descending, then donor_id ascending.
Tables
Table: Gift
| Column | Type |
|---|---|
| gift_id | int |
| donor_id | int |
| amount | int |
| gift_date | date |
Primary key: gift_id.
One row per donation received.
Examples
Example 1
Gift
| gift_id | donor_id | amount | gift_date |
|---|---|---|---|
| 1 | 7 | 500 | 2024-01-10 |
| 2 | 7 | 500 | 2024-02-10 |
| 3 | 7 | 500 | 2024-03-10 |
| 4 | 8 | 1000 | 2024-05-02 |
| 5 | 8 | 1500 | 2024-05-28 |
| 6 | 8 | 1000 | 2024-06-15 |
| 7 | 9 | 2000 | 2023-12-31 |
| 8 | 9 | 2000 | 2024-01-01 |
| 9 | 9 | 2500 | 2024-11-20 |
| 10 | 9 | 1000 | 2024-12-31 |
Output
| donor_id | months_given | total_2024 |
|---|---|---|
| 7 | 3 | 1500 |
| 9 | 3 | 5500 |
How to solve Recurring Donors Across Three Months of 2024
Restrict to 2024 first, then group by donor. The month count must be distinct: donor 8 in the example gave three times but in only two months (May twice and June), so they are not recurring. COUNT(DISTINCT MONTH(gift_date)) is safe because the filter has already fixed the year; without the filter, January 2024 and January 2025 would collapse into one month, and the key would have to include the year (DATE_FORMAT(gift_date, '%Y-%m')).
The year boundary is the other trap: 31 December 2023 is out and 31 December 2024 is in. A half-open range (>= '2024-01-01' AND < '2025-01-01') states it exactly, and it can use an index on gift_date, unlike YEAR(gift_date) = 2024.
The alternative first collapses each donor's gifts into one row per month and then counts those rows; the sum of the monthly sums is the yearly total. Both are one scan and a group by.
Reference solution (MySQL)
SELECT donor_id,
COUNT(DISTINCT MONTH(gift_date)) AS months_given,
SUM(amount) AS total_2024
FROM Gift
WHERE gift_date >= '2024-01-01' AND gift_date < '2025-01-01'
GROUP BY donor_id
HAVING COUNT(DISTINCT MONTH(gift_date)) >= 3
ORDER BY months_given DESC, donor_idAnother way
SELECT donor_id, COUNT(*) AS months_given, SUM(month_total) AS total_2024
FROM (SELECT donor_id, DATE_FORMAT(gift_date, '%Y-%m') AS ym, SUM(amount) AS month_total FROM Gift WHERE YEAR(gift_date) = 2024 GROUP BY donor_id, DATE_FORMAT(gift_date, '%Y-%m')) m
GROUP BY donor_id
HAVING COUNT(*) >= 3
ORDER BY months_given DESC, donor_id← Constituency Winners From Booth-Level Counts · Concert Sell-Through and Sales Status →