Date Each Frequent Flyer Qualified for Gold — SQL Problem
A frequent flyer earns Gold status in a calendar year on the day their tier points earned in that year first reach 25000 or more.
- Difficulty: Hard
- Topics: Window Functions, Dates, Joins
- Dialect: MySQL
- Problem: #547
Problem statement
A frequent flyer earns Gold status in a calendar year on the day their tier points earned in that year first reach 25000 or more. The count starts again from zero every 1 January, so points from December never help in January.
Return member_id, member_name, qualify_year and qualified_on (the date the year's running total first reached 25000) for every member and every year in which they qualified. Years in which a member stayed below 25000 are not listed. Return the rows in any order.
Tables
Table: FlyerMember
| Column | Type |
|---|---|
| member_id | int |
| member_name | varchar |
Primary key: member_id.
One row per member of the programme.
Table: TierPointCredit
| Column | Type |
|---|---|
| credit_id | int |
| member_id | int |
| flown_on | date |
| tier_points | int |
Primary key: credit_id.
One row per flown segment credited; a member can be credited several segments on one day. tier_points is positive.
Examples
Example 1
FlyerMember
| member_id | member_name |
|---|---|
| 1 | Rohan |
| 2 | Simran |
| 3 | David |
TierPointCredit
| credit_id | member_id | flown_on | tier_points |
|---|---|---|---|
| 1 | 1 | 2023-11-03 | 14000 |
| 2 | 1 | 2023-12-18 | 9000 |
| 3 | 1 | 2024-01-09 | 6000 |
| 4 | 1 | 2024-03-22 | 12000 |
| 5 | 1 | 2024-03-22 | 7000 |
| 6 | 2 | 2024-02-14 | 20000 |
| 7 | 2 | 2024-06-30 | 5000 |
| 8 | 2 | 2024-08-11 | 3000 |
| 9 | 3 | 2024-05-05 | 24000 |
Output
| member_id | member_name | qualify_year | qualified_on |
|---|---|---|---|
| 1 | Rohan | 2024 | 2024-03-22 |
| 2 | Simran | 2024 | 2024-06-30 |
How to solve Date Each Frequent Flyer Qualified for Gold
"Points earned this year so far" is a running total that resets — in SQL, a windowed SUM whose partition includes the reset key: PARTITION BY member_id, YEAR(flown_on). Ordering by flown_on, credit_id with ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW adds one credit at a time in a fixed order, so the running total is deterministic.
Once the running total crosses 25000 it never falls back, because points are positive. So the qualification date is simply the earliest date among rows whose running total is at least 25000 — MIN(flown_on) grouped by member and year. Years that never reach the line produce no qualifying rows and no output. Several credits on one day can be added in any order without changing the date reported: the total at the end of that day is the same, and the totals before that day are unaffected.
The alternative first nets each member's points per day, then uses a correlated subquery to compute the year-to-date total for each day and keeps the earliest day at or above 25000. It is quadratic per member and year; the window version needs a single sort. The 1 January reset is the edge case both handle through YEAR(flown_on).
Reference solution (MySQL)
WITH running AS (
SELECT member_id, YEAR(flown_on) AS qualify_year, flown_on,
SUM(tier_points) OVER (PARTITION BY member_id, YEAR(flown_on)
ORDER BY flown_on, credit_id
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS year_points
FROM TierPointCredit
)
SELECT m.member_id, m.member_name, r.qualify_year, MIN(r.flown_on) AS qualified_on
FROM running r
JOIN FlyerMember m ON m.member_id = r.member_id
WHERE r.year_points >= 25000
GROUP BY m.member_id, m.member_name, r.qualify_yearAnother way
WITH daily AS (
SELECT member_id, flown_on, SUM(tier_points) AS pts FROM TierPointCredit GROUP BY member_id, flown_on
)
SELECT m.member_id, m.member_name, YEAR(d.flown_on) AS qualify_year, MIN(d.flown_on) AS qualified_on
FROM daily d
JOIN FlyerMember m ON m.member_id = d.member_id
WHERE (SELECT SUM(e.pts) FROM daily e
WHERE e.member_id = d.member_id AND YEAR(e.flown_on) = YEAR(d.flown_on) AND e.flown_on <= d.flown_on) >= 25000
GROUP BY m.member_id, m.member_name, YEAR(d.flown_on)← App Sessions That End in a Hotel Booking · Airbnb: Instant Book Listings for a Last-Minute Weekend →