Gamertags Breaking the Naming Policy — SQL Strings Problem
Trust and safety enforces three gamertag rules, checked in this order: a gamertag longer than 14 characters is too_long; one that contains a space is…
- Difficulty: Easy
- Topics: Strings, Conditional Logic
- Dialect: MySQL
- Problem: #176
Problem statement
Trust and safety enforces three gamertag rules, checked in this order: a gamertag longer than 14 characters is too_long; one that contains a space is has_space; one that contains the word admin or official in any letter case is impersonation. A gamertag gets the label of the first rule it breaks.
Return the columns player_id, gamertag and violation for every gamertag that breaks at least one rule, ordered by player_id.
Tables
Table: Player
| Column | Type |
|---|---|
| player_id | int |
| gamertag | varchar |
Primary key: player_id.
One row per player. Gamertags use plain ASCII letters, digits, spaces and underscores.
Examples
Example 1
Player
| player_id | gamertag |
|---|---|
| 1 | ShadowFang |
| 2 | TheRealGameAdmin |
| 3 | Chai And Chill |
| 4 | OfficialRiya |
| 5 | SniperSaanvi99 |
| 6 | xX_admin_Xx |
| 7 | NightOwlNikhil07 |
Output
| player_id | gamertag | violation |
|---|---|---|
| 2 | TheRealGameAdmin | too_long |
| 3 | Chai And Chill | has_space |
| 4 | OfficialRiya | impersonation |
| 6 | xX_admin_Xx | impersonation |
| 7 | NightOwlNikhil07 | too_long |
How to solve Gamertags Breaking the Naming Policy
The label is a searched CASE whose branches follow the policy in order — length first, then the space, then the reserved words. CASE returns the first branch that holds, which is what "the first rule it breaks" means: TheRealGameAdmin is 16 characters, so it is too_long even though it also contains admin.
Letter case must not decide the impersonation rule, so the gamertag is lower-cased with LOWER before searching for admin and official. LOCATE(needle, haystack) > 0 (or INSTR(haystack, needle) > 0, or LIKE '%admin%') tests for a substring. An underscore is a wildcard inside a LIKE pattern, which is one reason to prefer LOCATE for literal text.
Players who break nothing must be excluded. Either repeat the rules in WHERE, as the solution does, or compute the CASE without an ELSE in a subquery and keep the rows whose label is not NULL. Both are one scan; the boundary is strict, so a 14-character gamertag is allowed.
Reference solution (MySQL)
SELECT player_id, gamertag,
CASE
WHEN CHAR_LENGTH(gamertag) > 14 THEN 'too_long'
WHEN LOCATE(' ', gamertag) > 0 THEN 'has_space'
ELSE 'impersonation'
END AS violation
FROM Player
WHERE CHAR_LENGTH(gamertag) > 14
OR LOCATE(' ', gamertag) > 0
OR LOCATE('admin', LOWER(gamertag)) > 0
OR LOCATE('official', LOWER(gamertag)) > 0
ORDER BY player_idAnother way
SELECT player_id, gamertag, violation FROM (
SELECT player_id, gamertag,
CASE WHEN LENGTH(gamertag) > 14 THEN 'too_long'
WHEN INSTR(gamertag, ' ') > 0 THEN 'has_space'
WHEN INSTR(LOWER(gamertag), 'admin') > 0 OR INSTR(LOWER(gamertag), 'official') > 0 THEN 'impersonation'
END AS violation
FROM Player
) t WHERE violation IS NOT NULL
ORDER BY player_idAnother way
SELECT player_id, gamertag,
IF(CHAR_LENGTH(gamertag) > 14, 'too_long', IF(gamertag LIKE '% %', 'has_space', 'impersonation')) AS violation
FROM Player
WHERE CHAR_LENGTH(gamertag) > 14 OR gamertag LIKE '% %' OR LOWER(gamertag) LIKE '%admin%' OR LOWER(gamertag) LIKE '%official%'
ORDER BY player_id← In-Game Store Revenue by Item Category · Average Completed Match Length by Game Mode →