Players Who Never Queued for Ranked — SQL Joins Problem
Every time a player presses Play, the matchmaker writes a QueueTicket for the mode they chose.
- Difficulty: Easy
- Topics: Joins, Subqueries
- Dialect: MySQL
- Problem: #174
Problem statement
Every time a player presses Play, the matchmaker writes a QueueTicket for the mode they chose. The competitive team wants to invite players who have never queued for the Ranked 5v5 mode to a ranked tutorial. Tickets for other modes do not count, and a cancelled ranked ticket still counts as having queued.
Return the columns player_id, gamertag and signup_date, ordered by signup_date, then player_id.
Tables
Table: Player
| Column | Type |
|---|---|
| player_id | int |
| gamertag | varchar |
| signup_date | date |
Primary key: player_id.
One row per registered player.
Table: QueueTicket
| Column | Type |
|---|---|
| ticket_id | int |
| player_id | int |
| mode | enum(Battle Royale, Team Deathmatch, Ranked 5v5, Capture the Flag) |
| status | enum(matched, cancelled, timed_out) |
Primary key: ticket_id.
One row per time a player entered a matchmaking queue. player_id is always in Player.
Examples
Example 1
Player
| player_id | gamertag | signup_date |
|---|---|---|
| 1 | NoScopeNeha | 2024-11-02 |
| 2 | RushRohan | 2024-11-02 |
| 3 | BlazeIra | 2024-12-15 |
| 4 | GhostArjun | 2024-10-20 |
| 5 | AceAisha | 2025-01-08 |
QueueTicket
| ticket_id | player_id | mode | status |
|---|---|---|---|
| 101 | 1 | Ranked 5v5 | matched |
| 102 | 2 | Battle Royale | matched |
| 103 | 2 | Team Deathmatch | timed_out |
| 104 | 3 | Ranked 5v5 | cancelled |
| 105 | 1 | Battle Royale | matched |
Output
| player_id | gamertag | signup_date |
|---|---|---|
| 4 | GhostArjun | 2024-10-20 |
| 2 | RushRohan | 2024-11-02 |
| 5 | AceAisha | 2025-01-08 |
How to solve Players Who Never Queued for Ranked
This is an anti join with a condition on the inner side. LEFT JOIN QueueTicket to Player on the player and on mode = 'Ranked 5v5': a player with a ranked ticket is matched, and a player with none — including one who only played Battle Royale — comes through once with NULLs in every ticket column. WHERE q.ticket_id IS NULL keeps exactly those.
Putting the mode test in WHERE instead is the classic mistake: the rows of players without any ticket have q.mode NULL, the test fails, and the anti join collapses into an inner join that returns nothing useful. The condition about the joined table belongs in ON.
NOT EXISTS with the same two conditions in the subquery says it directly and is what most optimisers do with the LEFT JOIN. NOT IN is safe here only because QueueTicket.player_id is never NULL. The status column is a distractor: a cancelled ranked ticket still shows the player tried ranked. Sorting by signup date with the id as the tie-break makes the order fixed.
Reference solution (MySQL)
SELECT p.player_id, p.gamertag, p.signup_date
FROM Player p
LEFT JOIN QueueTicket q ON q.player_id = p.player_id AND q.mode = 'Ranked 5v5'
WHERE q.ticket_id IS NULL
ORDER BY p.signup_date, p.player_idAnother way
SELECT player_id, gamertag, signup_date FROM Player p WHERE NOT EXISTS (SELECT 1 FROM QueueTicket q WHERE q.player_id = p.player_id AND q.mode = 'Ranked 5v5') ORDER BY signup_date, player_idAnother way
SELECT player_id, gamertag, signup_date FROM Player WHERE player_id NOT IN (SELECT player_id FROM QueueTicket WHERE mode = 'Ranked 5v5') ORDER BY signup_date, player_id← Diamond and Master Players Who Went Quiet · In-Game Store Revenue by Item Category →