Loyalty Tier Headcount From Points Balance

Easy
530.

Loyalty Tier Headcount From Points Balance

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

ColumnType
member_idint
member_namevarchar
points_balanceint

Primary key: member_id.

One row per frequent-flyer account; points_balance is never NULL or negative.

Example

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

FlyerAccount

member_idmember_namepoints_balance
1Riya62000
2Nikhil20000
3Tanvi18750
4Aisha4999
5Vivaan5000
6Maria31200
7John1200

Output

tiermembers
Blue2
Gold2
Platinum1
Silver2
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 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

ColumnType
member_idint
member_namevarchar
points_balanceint

Primary key: member_id.

One row per frequent-flyer account; points_balance is never NULL or negative.

Examples

Example 1

FlyerAccount

member_idmember_namepoints_balance
1Riya62000
2Nikhil20000
3Tanvi18750
4Aisha4999
5Vivaan5000
6Maria31200
7John1200

Output

tiermembers
Blue2
Gold2
Platinum1
Silver2

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 tier

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