Skip to main content
Talentd

Find All Unique Email Domains - Solution & Explanation

EasyPremiumFree on FleetCodeDatabase4 min read
Practice this problem

Problem Statement

Table: Emails

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.

Write a solution to find all unique email domains and count the number of individuals associated with each domain. Consider only those domains that end with .com.

Return the result table orderd by email domains in ascending order.

The result format is in the following example.

 

Example 1:

Input: 
Emails table:
+-----+-----------------------+
| id  | email                 |
+-----+-----------------------+
| 336 | hwkiy@test.edu        |
| 489 | adcmaf@outlook.com    |
| 449 | vrzmwyum@yahoo.com    |
| 95  | tof@test.edu          |
| 320 | jxhbagkpm@example.org |
| 411 | zxcf@outlook.com      |
+----+------------------------+
Output: 
+--------------+-------+
| email_domain | count |
+--------------+-------+
| outlook.com  | 2     |
| yahoo.com    | 1     |  
+--------------+-------+
Explanation: 
- The valid domains ending with ".com" are only "outlook.com" and "yahoo.com", with respective counts of 2 and 1.
Output table is ordered by email_domains in ascending order.

Approach Overview

Problem Overview: Given a dataset of email addresses, extract the domain part (the text after @) and return the list of unique domains. The task focuses on string extraction and deduplication, which are common operations in database queries and string processing.

Approach 1: Extract Domain + Hash Set (O(n) time, O(n) space)

Iterate through each email and locate the @ character. Everything after this character is the domain. Insert the extracted domain into a hash set to automatically remove duplicates. Each insertion and lookup in the set runs in constant time on average, making the full scan linear. This approach is straightforward in languages like Python where a set handles uniqueness efficiently and pairs naturally with basic hash table operations.

Approach 2: Using SUBSTRING_INDEX + GROUP BY (O(n) time, O(k) space)

In SQL environments such as MySQL, domain extraction can be done directly during the query. The SUBSTRING_INDEX(email, '@', -1) function returns the substring after the @ symbol. Once the domain is extracted, use GROUP BY or DISTINCT to aggregate identical domains. The database engine scans the email column once and groups matching values, giving linear time relative to the number of rows. Space complexity depends on the number of unique domains k, since only grouped domain values are stored.

This SQL-based solution is usually preferred when working directly with relational datasets. It avoids exporting data to application code and lets the database engine handle string parsing and aggregation efficiently. The logic stays compact and leverages built-in functions optimized for column scans.

Recommended for interviews: The domain-extraction + hash set approach demonstrates clear reasoning about string parsing and deduplication. Interviewers typically expect you to split the string at @, store domains in a set, and return the unique results in O(n) time. The SQL SUBSTRING_INDEX method is the production-ready solution when the problem is framed as a database query.

Solution

First, we filter out all emails ending with .com, then use the SUBSTRING_INDEX function to extract the domain name of the email. Finally, we use GROUP BY to count the number of each domain.

Code

MySQLPython

MySQL

Python

Try this approach in the editor →

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
Extract Domain + Hash SetO(n)O(n)General programming solution when processing emails in application code
SUBSTRING_INDEX + GROUP BYO(n)O(k)Best for SQL databases where domain extraction and deduplication can happen directly in a query
SUBSTRING_INDEX + DISTINCTO(n)O(k)Simpler SQL query when only unique domains are required without aggregation

Video Solution

Leetcode 3059 - Learn RegEx in SQL - Find All Unique Email Domains | Everyday Data Science • Everyday Data Science • 312 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Find All Unique Email Domains easy or hard?
Find All Unique Email Domains is categorized as an Easy problem. The logic relies on basic string parsing and deduplication using sets or SQL grouping functions, making it approachable for beginners practicing database and string manipulation tasks.
Find All Unique Email Domains Python/Java solution
In Python, split each email using email.split('@')[1] and store the result in a set to remove duplicates. In Java, use String.split("@") and add domains to a HashSet. For SQL problems, use SUBSTRING_INDEX(email, '@', -1) combined with DISTINCT or GROUP BY.
How to solve Find All Unique Email Domains in O(n)?
Scan each email once and extract the substring after the '@' symbol. Insert the domain into a set (in Python/Java) or use SUBSTRING_INDEX with DISTINCT/GROUP BY in SQL. Both methods process each record once, resulting in linear O(n) complexity.
What is the best approach for Find All Unique Email Domains?
The most efficient approach extracts the domain from each email and removes duplicates. In SQL, use SUBSTRING_INDEX(email, '@', -1) with GROUP BY or DISTINCT to return unique domains in O(n) time. In application code, split the email string and store domains in a hash set.
Is Find All Unique Email Domains asked at Google/Amazon/Meta?
Problems involving email parsing, string extraction, and deduplication appear frequently in interviews at companies like Google and Amazon. The exact problem may vary, but the underlying skills—string manipulation, hashing, and SQL aggregation—are commonly tested.
What data structure is used in Find All Unique Email Domains?
A hash set is typically used in programming solutions to track unique domains efficiently. In database queries, uniqueness is handled through SQL operations such as GROUP BY or DISTINCT after extracting the domain with SUBSTRING_INDEX.
What is the time complexity of Find All Unique Email Domains?
The optimal solution runs in O(n) time where n is the number of email records. Each email is scanned once to extract the domain, and uniqueness is enforced using either SQL grouping or a hash set. Space complexity is O(k) where k is the number of unique domains.

Ready to solve this problem?

Practice Find All Unique Email Domains with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary