Battle Pass Progress Labels for Season 12 — SQL Problem
The season 12 battle pass has 100 tiers. The end-of-season email groups players by how far they got: tier 100 is Completed, tiers 70–99 are Final Stretch,…
- Difficulty: Easy
- Topics: Conditional Logic, Basics
- Dialect: MySQL
- Problem: #179
Problem statement
The season 12 battle pass has 100 tiers. The end-of-season email groups players by how far they got: tier 100 is Completed, tiers 70–99 are Final Stretch, tiers 30–69 are Midway and anything below 30 is Just Started. Rows of other seasons are ignored.
For every player with a row for season S12, return player_id, current_tier and progress, ordered by current_tier descending, then player_id ascending.
Tables
Table: BattlePass
| Column | Type |
|---|---|
| player_id | int |
| season | varchar |
| current_tier | int |
| has_premium | bool |
Primary key: player_id, season.
One row per player per season they opened the battle pass. current_tier is 0–100; has_premium is 1 for a bought premium track.
Examples
Example 1
BattlePass
| player_id | season | current_tier | has_premium |
|---|---|---|---|
| 11 | S12 | 100 | 1 |
| 12 | S12 | 70 | 1 |
| 13 | S12 | 69 | 0 |
| 14 | S11 | 100 | 1 |
| 15 | S12 | 29 | 0 |
| 16 | S12 | 30 | 0 |
| 17 | S12 | 70 | 0 |
Output
| player_id | current_tier | progress |
|---|---|---|
| 11 | 100 | Completed |
| 12 | 70 | Final Stretch |
| 17 | 70 | Final Stretch |
| 13 | 69 | Midway |
| 16 | 30 | Midway |
| 15 | 29 | Just Started |
How to solve Battle Pass Progress Labels for Season 12
Filter to season = 'S12' and compute the label with a searched CASE. Because CASE stops at the first true branch, the branches can be written from the top band down with only lower bounds: = 100, then >= 70 (which by then can only be 70–99), then >= 30, and ELSE for the rest. Writing the bands with explicit BETWEEN ranges is equally correct and does not depend on the order of the branches.
The boundaries are the part to get right: a player on tier 70 is in the Final Stretch, one on tier 69 is Midway. The example has a row on each side of each boundary.
Nested IF calls give the same result in MySQL. Season 11 rows — including a completed one — are excluded by the WHERE clause. The sort is on the tier, highest first, with the player id deciding ties. It is a single scan.
Reference solution (MySQL)
SELECT player_id, current_tier,
CASE
WHEN current_tier = 100 THEN 'Completed'
WHEN current_tier >= 70 THEN 'Final Stretch'
WHEN current_tier >= 30 THEN 'Midway'
ELSE 'Just Started'
END AS progress
FROM BattlePass
WHERE season = 'S12'
ORDER BY current_tier DESC, player_idAnother way
SELECT player_id, current_tier, IF(current_tier >= 100, 'Completed', IF(current_tier >= 70, 'Final Stretch', IF(current_tier >= 30, 'Midway', 'Just Started'))) AS progress FROM BattlePass WHERE season = 'S12' ORDER BY current_tier DESC, player_idAnother way
SELECT player_id, current_tier, CASE WHEN current_tier BETWEEN 0 AND 29 THEN 'Just Started' WHEN current_tier BETWEEN 30 AND 69 THEN 'Midway' WHEN current_tier BETWEEN 70 AND 99 THEN 'Final Stretch' ELSE 'Completed' END AS progress FROM BattlePass WHERE season = 'S12' ORDER BY current_tier DESC, player_id← Daily Active Players From the Login Log · Players Rated Above the Average MMR →