Loyalty Tier Headcount From Points Balance — SQL Problem
An airline's frequent-flyer tiers come from the points balance: Platinum at 50000 points or more, Gold at 20000 or more, Silver at 5000 or more, and Blue…
- Difficulty: Easy
- Topics: Conditional Logic, Aggregation
- Dialect: MySQL
- Problem: #530
Problem statement
An airline's frequent-flyer tiers come from the points balance: Platinum at 50000 points or more, Gold at 20000 or more, Silver at 5000 or more, and Blue below 5000.
Return tier and members (the number of members in that tier) for every tier that has at least one member. A balance exactly on a threshold belongs to the higher tier. Return the rows in any order.
Tables
Table: FlyerAccount
| Column | Type |
|---|---|
| member_id | int |
| member_name | varchar |
| points_balance | int |
Primary key: member_id.
One row per frequent-flyer account; points_balance is never NULL or negative.
Examples
Example 1
FlyerAccount
| member_id | member_name | points_balance |
|---|---|---|
| 1 | Riya | 62000 |
| 2 | Nikhil | 20000 |
| 3 | Tanvi | 18750 |
| 4 | Aisha | 4999 |
| 5 | Vivaan | 5000 |
| 6 | Maria | 31200 |
| 7 | John | 1200 |
Output
| tier | members |
|---|---|
| Blue | 2 |
| Gold | 2 |
| Platinum | 1 |
| Silver | 2 |
How to solve Loyalty Tier Headcount From Points Balance
The tiers are not stored, so each balance is first mapped to its tier with a CASE expression, and then the rows are counted per tier. Because CASE returns the first branch whose condition is true, testing the thresholds from the top down — 50000, then 20000, then 5000 — gives each member exactly one tier without writing both ends of every range. A balance of exactly 20000 hits >= 20000 and lands in Gold, as the statement requires.
Computing the tier in a derived table and grouping by its alias keeps the expression in one place. Grouping only produces groups for tiers that actually occur, so an empty tier never appears.
The alternative counts each tier with its own WHERE range and stitches the four counts together with UNION ALL, using HAVING COUNT(*) > 0 to drop empty tiers; it is four scans instead of one, and every range has to be written with both ends.
Reference solution (MySQL)
SELECT tier, COUNT(*) AS members
FROM (
SELECT CASE
WHEN points_balance >= 50000 THEN 'Platinum'
WHEN points_balance >= 20000 THEN 'Gold'
WHEN points_balance >= 5000 THEN 'Silver'
ELSE 'Blue'
END AS tier
FROM FlyerAccount
) t
GROUP BY tierAnother way
SELECT 'Platinum' AS tier, COUNT(*) AS members FROM FlyerAccount WHERE points_balance >= 50000 HAVING COUNT(*) > 0
UNION ALL SELECT 'Gold', COUNT(*) FROM FlyerAccount WHERE points_balance >= 20000 AND points_balance < 50000 HAVING COUNT(*) > 0
UNION ALL SELECT 'Silver', COUNT(*) FROM FlyerAccount WHERE points_balance >= 5000 AND points_balance < 20000 HAVING COUNT(*) > 0
UNION ALL SELECT 'Blue', COUNT(*) FROM FlyerAccount WHERE points_balance < 5000 HAVING COUNT(*) > 0← Hotel Rooms Priced Above the Average Tariff · Hotel Occupancy on New Year's Eve →