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
| Column | Type |
|---|---|
| player_id | int |
| gamertag | varchar |
| region | varchar |
| tier | enum(Bronze, Silver, Gold, Platinum, Diamond, Master) |
| last_login_at | datetime |
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_id | gamertag | region | tier | last_login_at |
|---|---|---|---|---|
| 1 | ShadowFang | IN | Diamond | 2025-02-11 21:40:00 |
| 2 | PixelPriya | SEA | Master | 2025-03-04 10:15:22 |
| 3 | LagLord | EU | Gold | 2024-12-30 08:00:00 |
| 4 | ClutchDiya | IN | Master | NULL |
| 5 | FrostDev | NA | Diamond | 2025-03-01 00:00:00 |
| 6 | KingKabir | IN | Diamond | 2025-02-28 23:59:59 |
Output
| player_id | gamertag |
|---|---|
| 1 | ShadowFang |
| 4 | ClutchDiya |
| 6 | KingKabir |
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_idAnother 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_idAnother 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 →