Players Who Never Queued for Ranked

Easy
174.

Players Who Never Queued for Ranked

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

ColumnType
player_idint
gamertagvarchar
signup_datedate

Primary key: player_id.

One row per registered player.

Table: QueueTicket

ColumnTypeValues
ticket_idint
player_idint
modeenumBattle Royale, Team Deathmatch, Ranked 5v5, Capture the Flag
statusenummatched, cancelled, timed_out

Primary key: ticket_id.

One row per time a player entered a matchmaking queue. player_id is always in Player.

Example

The rows must come back in the order shown.

Player

player_idgamertagsignup_date
1NoScopeNeha2024-11-02
2RushRohan2024-11-02
3BlazeIra2024-12-15
4GhostArjun2024-10-20
5AceAisha2025-01-08

QueueTicket

ticket_idplayer_idmodestatus
1011Ranked 5v5matched
1022Battle Royalematched
1032Team Deathmatchtimed_out
1043Ranked 5v5cancelled
1051Battle Royalematched

Output

player_idgamertagsignup_date
4GhostArjun2024-10-20
2RushRohan2024-11-02
5AceAisha2025-01-08
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.

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

ColumnType
player_idint
gamertagvarchar
signup_datedate

Primary key: player_id.

One row per registered player.

Table: QueueTicket

ColumnType
ticket_idint
player_idint
modeenum(Battle Royale, Team Deathmatch, Ranked 5v5, Capture the Flag)
statusenum(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_idgamertagsignup_date
1NoScopeNeha2024-11-02
2RushRohan2024-11-02
3BlazeIra2024-12-15
4GhostArjun2024-10-20
5AceAisha2025-01-08

QueueTicket

ticket_idplayer_idmodestatus
1011Ranked 5v5matched
1022Battle Royalematched
1032Team Deathmatchtimed_out
1043Ranked 5v5cancelled
1051Battle Royalematched

Output

player_idgamertagsignup_date
4GhostArjun2024-10-20
2RushRohan2024-11-02
5AceAisha2025-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_id

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

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