Gamertags Breaking the Naming Policy

Easy
176.

Gamertags Breaking the Naming Policy

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

ColumnType
player_idint
gamertagvarchar

Primary key: player_id.

One row per player. Gamertags use plain ASCII letters, digits, spaces and underscores.

Example

The rows must come back in the order shown.

Player

player_idgamertag
1ShadowFang
2TheRealGameAdmin
3Chai And Chill
4OfficialRiya
5SniperSaanvi99
6xX_admin_Xx
7NightOwlNikhil07

Output

player_idgamertagviolation
2TheRealGameAdmintoo_long
3Chai And Chillhas_space
4OfficialRiyaimpersonation
6xX_admin_Xximpersonation
7NightOwlNikhil07too_long
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.

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

ColumnType
player_idint
gamertagvarchar

Primary key: player_id.

One row per player. Gamertags use plain ASCII letters, digits, spaces and underscores.

Examples

Example 1

Player

player_idgamertag
1ShadowFang
2TheRealGameAdmin
3Chai And Chill
4OfficialRiya
5SniperSaanvi99
6xX_admin_Xx
7NightOwlNikhil07

Output

player_idgamertagviolation
2TheRealGameAdmintoo_long
3Chai And Chillhas_space
4OfficialRiyaimpersonation
6xX_admin_Xximpersonation
7NightOwlNikhil07too_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_id

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

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