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
| Column | Type |
|---|---|
| member_id | int |
| member_name | varchar |
| tier | varchar |
Primary key: member_id.
One row per loyalty account.
Table: PointsLedger
| Column | Type |
|---|---|
| entry_id | int |
| member_id | int |
| entry_date | date |
| entry_type | enum(earn, redeem) |
| points | int |
Primary key: entry_id.
points is always positive; entry_type says whether it was added or spent.
Examples
Example 1
Member
| member_id | member_name | tier |
|---|---|---|
| 1 | Priya | Gold |
| 2 | Vikram | Silver |
| 3 | Sara | Silver |
| 4 | Arjun | Gold |
| 5 | Noah | Blue |
PointsLedger
| entry_id | member_id | entry_date | entry_type | points |
|---|---|---|---|---|
| 1 | 1 | 2023-11-02 | earn | 4200 |
| 2 | 1 | 2024-03-15 | redeem | 1500 |
| 3 | 2 | 2024-01-20 | earn | 2600 |
| 4 | 2 | 2024-07-01 | earn | 800 |
| 5 | 3 | 2023-12-05 | earn | 3000 |
| 6 | 3 | 2024-02-10 | redeem | 3000 |
| 7 | 4 | 2024-06-30 | earn | 950 |
Output
| member_id | member_name | last_activity | points_expiring |
|---|---|---|---|
| 1 | Priya | 2024-03-15 | 2700 |
| 4 | Arjun | 2024-06-30 | 950 |
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 > 0Another 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) > 0Another 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 →