Dream11: Multiple Accounts Behind One Gmail Address
Dream11's fair-play team looks for people who open several accounts to claim sign-up bonuses repeatedly.
- Difficulty: Medium
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #640
Problem statement
Dream11's fair-play team looks for people who open several accounts to claim sign-up bonuses repeatedly. Gmail delivers mail for ri.ya+ipl@gmail.com and riya@gmail.com to the same inbox: it ignores dots in the part before @ and anything from a + onwards.
Consider only addresses ending in @gmail.com. Normalise each by removing everything from the first + up to the @, removing the dots of the part before the @, and keeping @gmail.com. For every normalised address shared by two or more accounts, return canonical_email, accounts and first_user_id (the smallest user_id among them). Order by accounts descending, then by canonical_email.
A practice scenario set at Dream11: the tables and data are invented, and this is not a question from, or endorsed by, Dream11.
Tables
Table: AppUser
| Column | Type |
|---|---|
| user_id | int |
| varchar | |
| signup_date | date |
Primary key: user_id.
Emails are stored in lower case; each address is registered once.
Examples
Example 1
AppUser
| user_id | signup_date | |
|---|---|---|
| 1 | riya.sharma@gmail.com | 2025-03-01 |
| 2 | riyasharma+ipl@gmail.com | 2025-03-04 |
| 3 | r.i.y.a.sharma+bonus2@gmail.com | 2025-03-05 |
| 4 | riya.sharma@yahoo.co.in | 2025-03-05 |
| 5 | karan.mehta@gmail.com | 2025-03-06 |
| 6 | karanmehta@gmail.com | 2025-03-07 |
| 7 | dev.nair@outlook.com | 2025-03-07 |
| 8 | devnair@outlook.com | 2025-03-08 |
| 9 | zara@gmail.com | 2025-03-09 |
Output
| canonical_email | accounts | first_user_id |
|---|---|---|
| riyasharma@gmail.com | 3 | 1 |
| karanmehta@gmail.com | 2 | 5 |
How to solve Dream11: Multiple Accounts Behind One Gmail Address
The problem is mostly string surgery, then an ordinary duplicate search. For each Gmail address, take the local part with SUBSTRING_INDEX(email, '@', 1), drop any tag with SUBSTRING_INDEX(…, '+', 1) (everything before the first +; the whole string if there is none), remove the dots with REPLACE(…, '.', ''), and glue @gmail.com back on.
The order of operations matters: if the dots were removed first, a tag such as +dream.11 would be altered before being cut off — harmless here, but cutting first is the faithful reading of the rule and keeps each step simple. Other domains are filtered out by LIKE '%@gmail.com', because Outlook and Yahoo treat dots as significant: dev.nair@outlook.com and devnair@outlook.com are different people.
With the canonical address computed, GROUP BY with HAVING COUNT(*) >= 2 finds the shared inboxes and MIN(user_id) the original account. The alternative extracts the local part with LEFT/LOCATE and handles the tag with an IF. Both are one scan.
Reference solution (MySQL)
WITH g AS (
SELECT user_id,
CONCAT(REPLACE(SUBSTRING_INDEX(SUBSTRING_INDEX(email, '@', 1), '+', 1), '.', ''), '@gmail.com') AS canonical_email
FROM AppUser
WHERE email LIKE '%@gmail.com'
)
SELECT canonical_email, COUNT(*) AS accounts, MIN(user_id) AS first_user_id
FROM g
GROUP BY canonical_email
HAVING COUNT(*) >= 2
ORDER BY accounts DESC, canonical_emailAnother way
SELECT canonical_email, COUNT(*) AS accounts, MIN(user_id) AS first_user_id FROM (
SELECT user_id, CONCAT(REPLACE(IF(LOCATE('+', loc) > 0, LEFT(loc, LOCATE('+', loc) - 1), loc), '.', ''), '@gmail.com') AS canonical_email
FROM (SELECT user_id, LEFT(email, LOCATE('@', email) - 1) AS loc FROM AppUser WHERE RIGHT(email, 10) = '@gmail.com') a) b
GROUP BY canonical_email HAVING COUNT(*) > 1
ORDER BY accounts DESC, canonical_email← Dream11: Each Player's Form Over Their Last Three Matches · Dream11: Contest Prizes When Tied Teams Share a Rank →