Find All Unique Email Domains - Practice Coding Problems

Find All Unique Email Domains - Problem

Database Easy

You are given a table Emails that contains email addresses. Your task is to find all unique email domains that end with .com and count how many individuals are associated with each domain.

Requirements:

Extract the domain part from each email address
Filter domains that end with .com
Count the number of emails for each domain
Return results ordered by domain name in ascending order

Table Schema

Emails

Column Name	Type	Description
`id` PK	int	Primary key, unique identifier for each email
`email`	varchar	Email address (no uppercase letters)

Primary Key: id

Note: Each row contains a unique email address

Input & Output

Example 1 — Multiple Domains

Input Table:

id	email
1	[email protected]
2	[email protected]
3	[email protected]
4	[email protected]
5	[email protected]

Output:

email_domain	user_count
gmail.com	2
outlook.com	1
yahoo.com	1

💡 Note:

From 5 emails, we extract domains and filter for .com domains only. gmail.com appears twice (alice and carol), while yahoo.com and outlook.com each appear once. company.org is excluded because it doesn't end with .com. Results are ordered alphabetically by domain.

Example 2 — Single Domain

Input Table:

id	email
1	[email protected]
2	[email protected]
3	[email protected]

Output:

email_domain	user_count
example.com	2

💡 Note:

Only example.com qualifies as it ends with .com and appears twice. test.net is filtered out because it doesn't end with .com.

Constraints

1 ≤ id ≤ 10000
email contains valid email format with @ symbol
email contains only lowercase letters, digits, and special characters
Each email address is unique

Visualization

Tap to expand

Understanding the Visualization

Input

Email table with id and email columns

Extract & Filter

Get domains ending with .com

Group & Count

Count emails per domain

Key Takeaway

🎯 Key Insight: Use string functions to extract substrings and GROUP BY to aggregate data efficiently

Asked in

A Amazon 28 M Microsoft 22 G Google 18

Use string functions like SUBSTRING and POSITION to extract domain from email addresses, filter for .com domains with LIKE, then GROUP BY domain to count occurrences.

Table Schema

Emails

Column Name	Type	Description
`id` PK	int	Primary key, unique identifier for each email
`email`	varchar	Email address (no uppercase letters)

Primary Key: id

Note: Each row contains a unique email address

Common Approaches

Approach	Time	Space	Notes
✓ GROUP BY with String Functions	O(n)	O(k)	Extract domain using string functions, filter .com domains, and count occurrences

GROUP BY with String Functions — Algorithm Steps

Step 1: Extract domain using SUBSTRING and POSITION after '@' symbol
Step 2: Filter domains ending with '.com' using LIKE operator
Step 3: Group by domain and count occurrences
Step 4: Order results by domain name ascending

Visualization

Tap to expand

Step-by-Step Walkthrough

Extract

Get domain after @

Filter

Keep only .com domains

Group

Count by domain

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n)

Single pass through table with string operations

✓ Linear Growth

Space Complexity

O(k)

Storage for k unique domains

✓ Linear Space

23.4K Views

Medium Frequency

~12 min Avg. Time

892 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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

GROUP BY with String Functions — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler