Email Domains Behind Unverified Sign-ups

Medium
188.

Email Domains Behind Unverified Sign-ups

Bot farms create accounts in bulk to farm the new-player rewards and never verify their email. Trust and safety wants the email domains (everything after the @) that have at least 2 unverified accounts. Accounts that signed up with a phone number have email NULL and are ignored; verified accounts do not count.

Return email_domain and unverified_accounts, ordered by unverified_accounts descending, then email_domain ascending. Emails are stored in lower case.

Tables

Table: Account

ColumnType
player_idint
emailvarchar
email_verifiedbool
created_ondate

Primary key: player_id.

email is NULL for a phone sign-up; when present it contains exactly one @. email_verified is 1 once the player clicked the link.

Example

The rows must come back in the order shown.

Account

player_idemailemail_verifiedcreated_on
1neha.gamer@gmail.com12025-02-01
2x9k2@tempmail.dev02025-02-01
3q81z@tempmail.dev02025-02-01
4p0p0@tempmail.dev02025-02-02
5rohan99@gmail.com02025-02-03
6NULL02025-02-03
7bot77@mailbox.in02025-02-03
8bot78@mailbox.in02025-02-03
9kavya@outlook.com02025-02-04

Output

email_domainunverified_accounts
tempmail.dev3
mailbox.in2
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.

Email Domains Behind Unverified Sign-ups — SQL Problem

Bot farms create accounts in bulk to farm the new-player rewards and never verify their email.

  • Difficulty: Medium
  • Topics: Strings, Aggregation
  • Dialect: MySQL
  • Problem: #188

Problem statement

Bot farms create accounts in bulk to farm the new-player rewards and never verify their email. Trust and safety wants the email domains (everything after the @) that have at least 2 unverified accounts. Accounts that signed up with a phone number have email NULL and are ignored; verified accounts do not count.

Return email_domain and unverified_accounts, ordered by unverified_accounts descending, then email_domain ascending. Emails are stored in lower case.

Tables

Table: Account

ColumnType
player_idint
emailvarchar
email_verifiedbool
created_ondate

Primary key: player_id.

email is NULL for a phone sign-up; when present it contains exactly one @. email_verified is 1 once the player clicked the link.

Examples

Example 1

Account

player_idemailemail_verifiedcreated_on
1neha.gamer@gmail.com12025-02-01
2x9k2@tempmail.dev02025-02-01
3q81z@tempmail.dev02025-02-01
4p0p0@tempmail.dev02025-02-02
5rohan99@gmail.com02025-02-03
6NULL02025-02-03
7bot77@mailbox.in02025-02-03
8bot78@mailbox.in02025-02-03
9kavya@outlook.com02025-02-04

Output

email_domainunverified_accounts
tempmail.dev3
mailbox.in2

How to solve Email Domains Behind Unverified Sign-ups

Extract the domain, then group. SUBSTRING_INDEX(email, '@', -1) returns the part of the string after the last @; since every email has exactly one, that is the domain. SUBSTRING(email, LOCATE('@', email) + 1) and RIGHT(email, CHAR_LENGTH(email) - INSTR(email, '@')) compute the same thing from the position of the @.

Filter before grouping: only unverified rows (email_verified = 0) with a non-NULL email. A phone sign-up has no domain and would otherwise form a NULL group. Then group by the extracted domain and keep the groups with COUNT(*) >= 2 in HAVING — a single unverified Gmail account is a normal player who forgot to click a link, not a farm.

The sort puts the busiest domain first, with the name breaking ties. The work is one scan plus string functions per row; in production you would store the domain in its own indexed column.

Reference solution (MySQL)

SELECT SUBSTRING_INDEX(email, '@', -1) AS email_domain, COUNT(*) AS unverified_accounts
FROM Account
WHERE email IS NOT NULL AND email_verified = 0
GROUP BY SUBSTRING_INDEX(email, '@', -1)
HAVING COUNT(*) >= 2
ORDER BY unverified_accounts DESC, email_domain

Another way

SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS email_domain, COUNT(*) AS unverified_accounts FROM Account WHERE email_verified = 0 AND email LIKE '%@%' GROUP BY SUBSTRING(email, LOCATE('@', email) + 1) HAVING COUNT(*) >= 2 ORDER BY unverified_accounts DESC, email_domain

Another way

SELECT email_domain, COUNT(*) AS unverified_accounts FROM (SELECT RIGHT(email, CHAR_LENGTH(email) - INSTR(email, '@')) AS email_domain FROM Account WHERE email IS NOT NULL AND email_verified <> 1) d GROUP BY email_domain HAVING COUNT(*) > 1 ORDER BY unverified_accounts DESC, email_domain

← Guilds With Three Active Members in the Last Week · Guild Members Above Their Guild's Average Points →