Change Between a Donor's First and Latest Gift

Medium
90.

Change Between a Donor's First and Latest Gift

MediumWindow Functions

MySQL

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

ColumnType
gift_idint
donor_idint
amountint
gift_datedate

Primary key: gift_id.

One row per donation in rupees.

Example

The rows must come back in the order shown.

donor_gifts

gift_iddonor_idamountgift_date
110110002024-01-15
210115002024-06-15
310250002024-02-01
410320002024-03-10
51035002024-03-10
610112002024-12-15
710430002024-05-05
810430002024-09-05

Output

donor_idfirst_amountlatest_amountchange_amount
10110001200200
1032000500-1500
104300030000
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.

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

ColumnType
gift_idint
donor_idint
amountint
gift_datedate

Primary key: gift_id.

One row per donation in rupees.

Examples

Example 1

donor_gifts

gift_iddonor_idamountgift_date
110110002024-01-15
210115002024-06-15
310250002024-02-01
410320002024-03-10
51035002024-03-10
610112002024-12-15
710430002024-05-05
810430002024-09-05

Output

donor_idfirst_amountlatest_amountchange_amount
10110001200200
1032000500-1500
104300030000

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_id

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

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