Date Each Frequent Flyer Qualified for Gold

Hard
547.

Date Each Frequent Flyer Qualified for Gold

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

ColumnType
member_idint
member_namevarchar

Primary key: member_id.

One row per member of the programme.

Table: TierPointCredit

ColumnType
credit_idint
member_idint
flown_ondate
tier_pointsint

Primary key: credit_id.

One row per flown segment credited; a member can be credited several segments on one day. tier_points is positive.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

FlyerMember

member_idmember_name
1Rohan
2Simran
3David

TierPointCredit

credit_idmember_idflown_ontier_points
112023-11-0314000
212023-12-189000
312024-01-096000
412024-03-2212000
512024-03-227000
622024-02-1420000
722024-06-305000
822024-08-113000
932024-05-0524000

Output

member_idmember_namequalify_yearqualified_on
1Rohan20242024-03-22
2Simran20242024-06-30
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.

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

ColumnType
member_idint
member_namevarchar

Primary key: member_id.

One row per member of the programme.

Table: TierPointCredit

ColumnType
credit_idint
member_idint
flown_ondate
tier_pointsint

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_idmember_name
1Rohan
2Simran
3David

TierPointCredit

credit_idmember_idflown_ontier_points
112023-11-0314000
212023-12-189000
312024-01-096000
412024-03-2212000
512024-03-227000
622024-02-1420000
722024-06-305000
822024-08-113000
932024-05-0524000

Output

member_idmember_namequalify_yearqualified_on
1Rohan20242024-03-22
2Simran20242024-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_year

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