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
| Column | Type |
|---|---|
| player_id | int |
| varchar | |
| email_verified | bool |
| created_on | date |
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_id | email_verified | created_on | |
|---|---|---|---|
| 1 | neha.gamer@gmail.com | 1 | 2025-02-01 |
| 2 | x9k2@tempmail.dev | 0 | 2025-02-01 |
| 3 | q81z@tempmail.dev | 0 | 2025-02-01 |
| 4 | p0p0@tempmail.dev | 0 | 2025-02-02 |
| 5 | rohan99@gmail.com | 0 | 2025-02-03 |
| 6 | NULL | 0 | 2025-02-03 |
| 7 | bot77@mailbox.in | 0 | 2025-02-03 |
| 8 | bot78@mailbox.in | 0 | 2025-02-03 |
| 9 | kavya@outlook.com | 0 | 2025-02-04 |
Output
| email_domain | unverified_accounts |
|---|---|
| tempmail.dev | 3 |
| mailbox.in | 2 |
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_domainAnother 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_domainAnother 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 →