Dream11: Multiple Accounts Behind One Gmail Address

Medium
640.

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. 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

ColumnType
user_idint
emailvarchar
signup_datedate

Primary key: user_id.

Emails are stored in lower case; each address is registered once.

Example

The rows must come back in the order shown.

AppUser

user_idemailsignup_date
1riya.sharma@gmail.com2025-03-01
2riyasharma+ipl@gmail.com2025-03-04
3r.i.y.a.sharma+bonus2@gmail.com2025-03-05
4riya.sharma@yahoo.co.in2025-03-05
5karan.mehta@gmail.com2025-03-06
6karanmehta@gmail.com2025-03-07
7dev.nair@outlook.com2025-03-07
8devnair@outlook.com2025-03-08
9zara@gmail.com2025-03-09

Output

canonical_emailaccountsfirst_user_id
riyasharma@gmail.com31
karanmehta@gmail.com25
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.

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

ColumnType
user_idint
emailvarchar
signup_datedate

Primary key: user_id.

Emails are stored in lower case; each address is registered once.

Examples

Example 1

AppUser

user_idemailsignup_date
1riya.sharma@gmail.com2025-03-01
2riyasharma+ipl@gmail.com2025-03-04
3r.i.y.a.sharma+bonus2@gmail.com2025-03-05
4riya.sharma@yahoo.co.in2025-03-05
5karan.mehta@gmail.com2025-03-06
6karanmehta@gmail.com2025-03-07
7dev.nair@outlook.com2025-03-07
8devnair@outlook.com2025-03-08
9zara@gmail.com2025-03-09

Output

canonical_emailaccountsfirst_user_id
riyasharma@gmail.com31
karanmehta@gmail.com25

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_email

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