Loyalty Points Expiring for Inactive Members

Medium
539.

Loyalty Points Expiring for Inactive Members

A hotel loyalty programme's points expire when an account has had no earn or redeem activity for 12 months. The expiry run on 2025-06-30 wipes every account whose last ledger entry is before 2024-07-01 and whose balance (earned points minus redeemed points) is greater than 0.

Return member_id, member_name, last_activity (the date of the member's latest entry) and points_expiring (the balance) for those members. Members with no ledger entries have nothing to expire. Return the rows in any order.

Tables

Table: Member

ColumnType
member_idint
member_namevarchar
tiervarchar

Primary key: member_id.

One row per loyalty account.

Table: PointsLedger

ColumnTypeValues
entry_idint
member_idint
entry_datedate
entry_typeenumearn, redeem
pointsint

Primary key: entry_id.

points is always positive; entry_type says whether it was added or spent.

Example

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

Member

member_idmember_nametier
1PriyaGold
2VikramSilver
3SaraSilver
4ArjunGold
5NoahBlue

PointsLedger

entry_idmember_identry_dateentry_typepoints
112023-11-02earn4200
212024-03-15redeem1500
322024-01-20earn2600
422024-07-01earn800
532023-12-05earn3000
632024-02-10redeem3000
742024-06-30earn950

Output

member_idmember_namelast_activitypoints_expiring
1Priya2024-03-152700
4Arjun2024-06-30950
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.

Loyalty Points Expiring for Inactive Members — SQL Problem

A hotel loyalty programme's points expire when an account has had no earn or redeem activity for 12 months.

  • Difficulty: Medium
  • Topics: Subqueries, Dates, Conditional Logic
  • Dialect: MySQL
  • Problem: #539

Problem statement

A hotel loyalty programme's points expire when an account has had no earn or redeem activity for 12 months. The expiry run on 2025-06-30 wipes every account whose last ledger entry is before 2024-07-01 and whose balance (earned points minus redeemed points) is greater than 0.

Return member_id, member_name, last_activity (the date of the member's latest entry) and points_expiring (the balance) for those members. Members with no ledger entries have nothing to expire. Return the rows in any order.

Tables

Table: Member

ColumnType
member_idint
member_namevarchar
tiervarchar

Primary key: member_id.

One row per loyalty account.

Table: PointsLedger

ColumnType
entry_idint
member_idint
entry_datedate
entry_typeenum(earn, redeem)
pointsint

Primary key: entry_id.

points is always positive; entry_type says whether it was added or spent.

Examples

Example 1

Member

member_idmember_nametier
1PriyaGold
2VikramSilver
3SaraSilver
4ArjunGold
5NoahBlue

PointsLedger

entry_idmember_identry_dateentry_typepoints
112023-11-02earn4200
212024-03-15redeem1500
322024-01-20earn2600
422024-07-01earn800
532023-12-05earn3000
632024-02-10redeem3000
742024-06-30earn950

Output

member_idmember_namelast_activitypoints_expiring
1Priya2024-03-152700
4Arjun2024-06-30950

How to solve Loyalty Points Expiring for Inactive Members

Two facts are needed per member — when they last did anything, and what their balance is — and both come from one pass over the ledger grouped by member_id: MAX(entry_date) for the last activity and SUM(CASE WHEN entry_type = 'earn' THEN points ELSE -points END) for the balance, since redemptions subtract. Doing this in a derived table and joining it to Member keeps the outer query a simple filter.

The filter applies both expiry conditions: last_activity < '2024-07-01' (strictly — an entry on 2024-07-01 is within the 12 months and saves the account) and balance > 0 (an account already at zero has nothing to wipe). Members with no ledger rows have no summary row, so the inner join leaves them out, as the statement says.

The second alternative is the same idea with GROUP BY … HAVING on the joined rows. The first is written as correlated subqueries: NOT EXISTS an entry on or after the cut-off states "inactive" directly, and scalar subqueries fetch the date and balance. It reads like the policy but scans the ledger several times per member; the grouped forms read it once.

Reference solution (MySQL)

SELECT m.member_id, m.member_name, l.last_activity, l.balance AS points_expiring
FROM Member m
JOIN (
  SELECT member_id,
         MAX(entry_date) AS last_activity,
         SUM(CASE WHEN entry_type = 'earn' THEN points ELSE -points END) AS balance
  FROM PointsLedger
  GROUP BY member_id
) l ON l.member_id = m.member_id
WHERE l.last_activity < '2024-07-01' AND l.balance > 0

Another way

SELECT m.member_id, m.member_name,
       (SELECT MAX(entry_date) FROM PointsLedger x WHERE x.member_id = m.member_id) AS last_activity,
       (SELECT SUM(IF(entry_type = 'earn', points, -points)) FROM PointsLedger x WHERE x.member_id = m.member_id) AS points_expiring
FROM Member m
WHERE EXISTS (SELECT 1 FROM PointsLedger x WHERE x.member_id = m.member_id)
  AND NOT EXISTS (SELECT 1 FROM PointsLedger x WHERE x.member_id = m.member_id AND x.entry_date >= '2024-07-01')
  AND (SELECT SUM(IF(entry_type = 'earn', points, -points)) FROM PointsLedger x WHERE x.member_id = m.member_id) > 0

Another way

SELECT m.member_id, m.member_name, MAX(l.entry_date) AS last_activity,
       SUM(CASE l.entry_type WHEN 'redeem' THEN -l.points ELSE l.points END) AS points_expiring
FROM Member m JOIN PointsLedger l ON l.member_id = m.member_id
GROUP BY m.member_id, m.member_name
HAVING MAX(l.entry_date) < '2024-07-01' AND SUM(CASE l.entry_type WHEN 'redeem' THEN -l.points ELSE l.points END) > 0

← Double-Booked Hotel Rooms · Connecting Itineraries per City Pair →