Diamond and Master Players Who Went Quiet

Easy
173.

Diamond and Master Players Who Went Quiet

EasyBasicsDates

MySQL

The live-ops team wants to send a comeback reward to high-tier players who have stopped logging in. A player qualifies when their tier is Diamond or Master and their last login was before 1 March 2025 (last_login_at < '2025-03-01'). A NULL last_login_at means no login was recorded since the account migration, and such a player qualifies too.

Return the columns player_id and gamertag, ordered by player_id.

Tables

Table: Player

ColumnTypeValues
player_idint
gamertagvarchar
regionvarchar
tierenumBronze, Silver, Gold, Platinum, Diamond, Master
last_login_atdatetime

Primary key: player_id.

One row per player account. last_login_at is NULL when no login was recorded after the migration.

Example

The rows must come back in the order shown.

Player

player_idgamertagregiontierlast_login_at
1ShadowFangINDiamond2025-02-11 21:40:00
2PixelPriyaSEAMaster2025-03-04 10:15:22
3LagLordEUGold2024-12-30 08:00:00
4ClutchDiyaINMasterNULL
5FrostDevNADiamond2025-03-01 00:00:00
6KingKabirINDiamond2025-02-28 23:59:59

Output

player_idgamertag
1ShadowFang
4ClutchDiya
6KingKabir
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.

Diamond and Master Players Who Went Quiet — SQL Problem

The live-ops team wants to send a comeback reward to high-tier players who have stopped logging in.

  • Difficulty: Easy
  • Topics: Basics, Dates
  • Dialect: MySQL
  • Problem: #173

Problem statement

The live-ops team wants to send a comeback reward to high-tier players who have stopped logging in. A player qualifies when their tier is Diamond or Master and their last login was before 1 March 2025 (last_login_at < '2025-03-01'). A NULL last_login_at means no login was recorded since the account migration, and such a player qualifies too.

Return the columns player_id and gamertag, ordered by player_id.

Tables

Table: Player

ColumnType
player_idint
gamertagvarchar
regionvarchar
tierenum(Bronze, Silver, Gold, Platinum, Diamond, Master)
last_login_atdatetime

Primary key: player_id.

One row per player account. last_login_at is NULL when no login was recorded after the migration.

Examples

Example 1

Player

player_idgamertagregiontierlast_login_at
1ShadowFangINDiamond2025-02-11 21:40:00
2PixelPriyaSEAMaster2025-03-04 10:15:22
3LagLordEUGold2024-12-30 08:00:00
4ClutchDiyaINMasterNULL
5FrostDevNADiamond2025-03-01 00:00:00
6KingKabirINDiamond2025-02-28 23:59:59

Output

player_idgamertag
1ShadowFang
4ClutchDiya
6KingKabir

How to solve Diamond and Master Players Who Went Quiet

This is a filter with one trap. The tier condition is a plain IN list. The login condition is "before 1 March 2025": datetimes are stored in a fixed YYYY-MM-DD HH:MM:SS format, so comparing with '2025-03-01' keeps everything up to 2025-02-28 23:59:59 and drops a login at exactly midnight on the first, which is not before the date.

The trap is NULL. NULL < '2025-03-01' is unknown, not true, so a player with no recorded login would silently disappear — and the statement says those players qualify. Add last_login_at IS NULL with an OR, and wrap the two login tests in parentheses: AND binds tighter than OR, so without them a Gold player with a NULL login would slip in.

An alternative replaces the NULL with a date far in the past through COALESCE, which turns the two tests into one comparison. A UNION of the two cases works as well. Each is one scan of the table.

Reference solution (MySQL)

SELECT player_id, gamertag
FROM Player
WHERE tier IN ('Diamond', 'Master')
  AND (last_login_at IS NULL OR last_login_at < '2025-03-01')
ORDER BY player_id

Another way

SELECT player_id, gamertag FROM Player WHERE (tier = 'Diamond' OR tier = 'Master') AND COALESCE(last_login_at, '1970-01-01 00:00:00') < '2025-03-01' ORDER BY player_id

Another way

SELECT player_id, gamertag FROM Player WHERE tier IN ('Diamond', 'Master') AND last_login_at IS NULL UNION ALL SELECT player_id, gamertag FROM Player WHERE tier IN ('Diamond', 'Master') AND DATE(last_login_at) < '2025-03-01' ORDER BY player_id

← Top Two Restaurants per Cuisine by Repeat-Customer Rate · Players Who Never Queued for Ranked →