Find All Unique Email Domains - Problem
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 | |
|---|---|
| 1 | alice@gmail.com |
| 2 | bob@yahoo.com |
| 3 | carol@gmail.com |
| 4 | dave@company.org |
| 5 | eve@outlook.com |
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 | |
|---|---|
| 1 | user1@example.com |
| 2 | user2@example.com |
| 3 | user3@test.net |
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 -
emailcontains valid email format with @ symbol -
emailcontains only lowercase letters, digits, and special characters - Each email address is unique
Visualization
Tap to expand
💡
Explanation
AI Ready
💡 Suggestion
Tab
to accept
Esc
to dismiss
// Output will appear here after running code