Change Between a Donor's First and Latest Gift — SQL Problem
The NGO's donor-care team wants to see whether repeat donors are giving more or less than when they started.
- Difficulty: Medium
- Topics: Window Functions
- Dialect: MySQL
- Problem: #90
Problem statement
The NGO's donor-care team wants to see whether repeat donors are giving more or less than when they started. When a donor gave twice on the same day, the gift with the smaller gift_id came first.
For every donor with at least two gifts, return donor_id, first_amount (their earliest gift), latest_amount (their most recent gift) and change_amount = latest_amount - first_amount, ordered by donor_id.
Tables
Table: donor_gifts
| Column | Type |
|---|---|
| gift_id | int |
| donor_id | int |
| amount | int |
| gift_date | date |
Primary key: gift_id.
One row per donation in rupees.
Examples
Example 1
donor_gifts
| gift_id | donor_id | amount | gift_date |
|---|---|---|---|
| 1 | 101 | 1000 | 2024-01-15 |
| 2 | 101 | 1500 | 2024-06-15 |
| 3 | 102 | 5000 | 2024-02-01 |
| 4 | 103 | 2000 | 2024-03-10 |
| 5 | 103 | 500 | 2024-03-10 |
| 6 | 101 | 1200 | 2024-12-15 |
| 7 | 104 | 3000 | 2024-05-05 |
| 8 | 104 | 3000 | 2024-09-05 |
Output
| donor_id | first_amount | latest_amount | change_amount |
|---|---|---|---|
| 101 | 1000 | 1200 | 200 |
| 103 | 2000 | 500 | -1500 |
| 104 | 3000 | 3000 | 0 |
How to solve Change Between a Donor's First and Latest Gift
First and last per group is a window problem. ROW_NUMBER() over each donor's gifts ordered by (gift_date, gift_id) marks the first gift with 1; the same numbering in descending order marks the latest with 1. Adding gift_id to the order is what makes it deterministic: donor 103 gave twice on 10 March, and the rule says gift 4 came first.
Grouping the numbered rows by donor and taking MAX(CASE WHEN from_first = 1 THEN amount END) picks the one marked amount (the CASE is NULL on all other rows, and MAX ignores NULLs). HAVING COUNT(*) >= 2 drops one-time donors, whose first and latest gift would be the same row.
FIRST_VALUE/LAST_VALUE work too, but LAST_VALUE needs the frame widened to UNBOUNDED FOLLOWING — with the default frame it only sees up to the current row and returns the current amount. The NOT EXISTS version finds the row with no earlier (or later) gift; it is quadratic per donor but uses no windows.
Reference solution (MySQL)
WITH ordered_gifts AS (
SELECT donor_id, amount,
ROW_NUMBER() OVER (PARTITION BY donor_id ORDER BY gift_date, gift_id) AS from_first,
ROW_NUMBER() OVER (PARTITION BY donor_id ORDER BY gift_date DESC, gift_id DESC) AS from_last
FROM donor_gifts
)
SELECT donor_id,
MAX(CASE WHEN from_first = 1 THEN amount END) AS first_amount,
MAX(CASE WHEN from_last = 1 THEN amount END) AS latest_amount,
MAX(CASE WHEN from_last = 1 THEN amount END) - MAX(CASE WHEN from_first = 1 THEN amount END) AS change_amount
FROM ordered_gifts
GROUP BY donor_id
HAVING COUNT(*) >= 2
ORDER BY donor_idAnother way
SELECT DISTINCT donor_id, first_amount, latest_amount, latest_amount - first_amount AS change_amount FROM (
SELECT donor_id,
FIRST_VALUE(amount) OVER (PARTITION BY donor_id ORDER BY gift_date, gift_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS first_amount,
LAST_VALUE(amount) OVER (PARTITION BY donor_id ORDER BY gift_date, gift_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS latest_amount,
COUNT(*) OVER (PARTITION BY donor_id) AS gifts
FROM donor_gifts
) t WHERE gifts >= 2
ORDER BY donor_idAnother way
SELECT d.donor_id, f.amount AS first_amount, l.amount AS latest_amount, l.amount - f.amount AS change_amount
FROM (SELECT donor_id FROM donor_gifts GROUP BY donor_id HAVING COUNT(*) >= 2) d
JOIN donor_gifts f ON f.donor_id = d.donor_id AND NOT EXISTS (SELECT 1 FROM donor_gifts x WHERE x.donor_id = f.donor_id AND (x.gift_date < f.gift_date OR (x.gift_date = f.gift_date AND x.gift_id < f.gift_id)))
JOIN donor_gifts l ON l.donor_id = d.donor_id AND NOT EXISTS (SELECT 1 FROM donor_gifts y WHERE y.donor_id = l.donor_id AND (y.gift_date > l.gift_date OR (y.gift_date = l.gift_date AND y.gift_id > l.gift_id)))
ORDER BY d.donor_id← Grievance Tickets per Ward From Reference Codes · Wards With Complaint Streaks of Three Days or More →