Battle Pass Progress Labels for Season 12

Easy
179.

Battle Pass Progress Labels for Season 12

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

ColumnType
player_idint
seasonvarchar
current_tierint
has_premiumbool

Primary key (together): 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.

Example

The rows must come back in the order shown.

BattlePass

player_idseasoncurrent_tierhas_premium
11S121001
12S12701
13S12690
14S111001
15S12290
16S12300
17S12700

Output

player_idcurrent_tierprogress
11100Completed
1270Final Stretch
1770Final Stretch
1369Midway
1630Midway
1529Just Started
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.

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

ColumnType
player_idint
seasonvarchar
current_tierint
has_premiumbool

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_idseasoncurrent_tierhas_premium
11S121001
12S12701
13S12690
14S111001
15S12290
16S12300
17S12700

Output

player_idcurrent_tierprogress
11100Completed
1270Final Stretch
1770Final Stretch
1369Midway
1630Midway
1529Just 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_id

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

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