Find All Unique Email Domains - Practice Coding Problems

Find All Unique Email Domains - Problem

Database Easy

🔍 Find All Unique Email Domains

You're tasked with analyzing email data to understand domain distribution! Given a table of emails, you need to extract and count unique email domains that end with .com.

📊 Database Schema

🎯 Your Mission

Write a SQL query to find all unique email domains that end with .com and count how many individuals are associated with each domain. Return results ordered by domain name in ascending order.

📝 Example

Input:

Emails table:
+----+------------------+
| id | email            |
+----+------------------+
| 1  | alice@gmail.com  |
| 2  | bob@yahoo.com    |
| 3  | charlie@gmail.com|
| 4  | david@yahoo.org  |
| 5  | eve@hotmail.com  |
+----+------------------+

Output:

+-------------+-------+
| domain      | count |
+-------------+-------+
| gmail.com   | 2     |
| hotmail.com | 1     |
| yahoo.com   | 1     |
+-------------+-------+

Input & Output

example_1.sql — Basic Case

$ Input: Emails table: +----+------------------+ | id | email | +----+------------------+ | 1 | alice@gmail.com | | 2 | bob@yahoo.com | | 3 | charlie@gmail.com| +----+------------------+

› Output: +-------------+-------+ | domain | count | +-------------+-------+ | gmail.com | 2 | | yahoo.com | 1 | +-------------+-------+

💡 Note: We extract domains from .com emails: gmail.com appears twice, yahoo.com once. Results are sorted alphabetically.

example_2.sql — Mixed Domains

$ Input: Emails table: +----+--------------------+ | id | email | +----+--------------------+ | 1 | user@example.com | | 2 | admin@test.org | | 3 | hello@world.net | | 4 | support@help.com | +----+--------------------+

› Output: +-------------+-------+ | domain | count | +-------------+-------+ | example.com | 1 | | help.com | 1 | +-------------+-------+

💡 Note: Only .com domains are included: example.com and help.com. The .org and .net domains are filtered out.

example_3.sql — Single Domain

$ Input: Emails table: +----+------------------+ | id | email | +----+------------------+ | 1 | user1@company.com| | 2 | user2@company.com| | 3 | user3@company.com| +----+------------------+

› Output: +-------------+-------+ | domain | count | +-------------+-------+ | company.com | 3 | +-------------+-------+

💡 Note: All emails belong to the same domain company.com, so we get one row with count 3.

Constraints

1 ≤ Number of emails ≤ 10⁴
Email addresses are valid and contain exactly one @ symbol
All email addresses are in lowercase
Only consider domains ending with .com
Domain names are case-sensitive (though all lowercase in this problem)

Visualization

Tap to expand

Understanding the Visualization

Extract Domain

Use string functions to extract the part after '@' from each email address

Filter .com

Keep only domains ending with '.com' using WHERE clause

Group & Count

GROUP BY domain automatically creates groups and COUNT(*) tallies each group

Sort Results

ORDER BY domain name to present results alphabetically

Key Takeaway

🎯 Key Insight: SQL's GROUP BY is like having automatic sorting bins that group identical domains and count them efficiently in a single database scan!

Asked in

G Google 25 f Meta 18 a Amazon 15 ⊞ Microsoft 12

The optimal solution uses SQL's GROUP BY clause with string functions to extract email domains in a single pass. We use SUBSTRING/SPLIT_PART to extract the domain portion after '@', filter for .com domains with WHERE, then GROUP BY domain and COUNT(*) to get frequencies. Finally, ORDER BY sorts results alphabetically. Time complexity: O(n) with efficient database indexing.

Common Approaches

Approach	Time	Space	Notes
✓ GROUP BY with Domain Extraction (Optimal)	O(n)	O(k)	Use SQL GROUP BY to extract domains and count in single pass
Brute Force (Manual Domain Extraction)	O(n²)	O(n)	Extract domains manually using string functions without grouping

GROUP BY with Domain Extraction (Optimal) — Algorithm Steps

Use SUBSTRING/RIGHT function to extract domain after '@' symbol
Filter emails ending with '.com' using WHERE clause
GROUP BY the extracted domain to automatically group identical domains
Use COUNT(*) to count rows in each group
ORDER BY domain name for sorted output

Visualization

Tap to expand

Step-by-Step Walkthrough

Extract & Filter

Extract domains from .com emails using string functions

GROUP BY Domain

Automatically group identical domains together

COUNT(*) Each Group

Count rows in each group efficiently

ORDER BY Domain

Sort final results by domain name

Code -

solution.c — C

-- Simple SQL Solution
SELECT 
    SUBSTR(email, INSTR(email, '@') + 1) as domain,
    COUNT(*) as count
FROM Emails 
WHERE email LIKE '%.com'
GROUP BY SUBSTR(email, INSTR(email, '@') + 1)
ORDER BY domain;

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through the data with efficient grouping

✓ Linear Growth

Space Complexity

O(k)

Where k is the number of unique domains (much smaller than n)

✓ Linear Space

28.5K Views

Medium Frequency

~12 min Avg. Time

840 Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

🔍 Find All Unique Email Domains

📊 Database Schema

🎯 Your Mission

📝 Example

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

GROUP BY with Domain Extraction (Optimal) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler