Recurring Donors Across Three Months of 2024

Medium
84.

Recurring Donors Across Three Months of 2024

MediumDatesAggregation

MySQL

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

ColumnType
gift_idint
donor_idint
amountint
gift_datedate

Primary key: gift_id.

One row per donation received.

Example

The rows must come back in the order shown.

Gift

gift_iddonor_idamountgift_date
175002024-01-10
275002024-02-10
375002024-03-10
4810002024-05-02
5815002024-05-28
6810002024-06-15
7920002023-12-31
8920002024-01-01
9925002024-11-20
10910002024-12-31

Output

donor_idmonths_giventotal_2024
731500
935500
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.

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

ColumnType
gift_idint
donor_idint
amountint
gift_datedate

Primary key: gift_id.

One row per donation received.

Examples

Example 1

Gift

gift_iddonor_idamountgift_date
175002024-01-10
275002024-02-10
375002024-03-10
4810002024-05-02
5815002024-05-28
6810002024-06-15
7920002023-12-31
8920002024-01-01
9925002024-11-20
10910002024-12-31

Output

donor_idmonths_giventotal_2024
731500
935500

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_id

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