Commercial Email Domain Census

EASY10 pts

A CRM intelligence team wants a domain-level count of business email usage across incoming leads.

You are given one table:

Emails

id: unique row identifier
email: lowercase email address

Task:

Extract the domain part of each email (substring after '@').
Keep only domains whose suffix is exactly '.com'.
Count how many rows belong to each remaining domain.

Output requirements:

Return exactly these columns in this order: email_domain, count
Sort rows by email_domain in ascending lexicographic order.

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Emails:
| id  | email                    |
|-----|--------------------------|
| 101 | rina@outlook.com         |
| 102 | noah@yahoo.com           |
| 103 | amir@outlook.com         |
| 104 | lucy@research.org        |
| 105 | mia@example.net          |
| 106 | devon@analytics.yahoo.com |

Output

[
  {"email_domain":"analytics.yahoo.com","count":1},
  {"email_domain":"outlook.com","count":2},
  {"email_domain":"yahoo.com","count":1}
]

Explanation

Only domains ending with '.com' are included. Domains are grouped and counted, then sorted alphabetically.

Example

Input

Emails:
| id | email              |
|----|--------------------|
| 1  | a@alpha.org        |
| 2  | b@beta.net         |
| 3  | c@campus.edu       |
| 4  | d@startup.io       |

Output

[]

Explanation

No domain ends with '.com', so the result is empty.

Example

Input

Emails:
| id | email                    |
|----|--------------------------|
| 11 | kim@finance.com          |
| 12 | lee@ops.company          |
| 13 | rai@mail.finance.com     |
| 14 | zen@alerts.finance.com   |
| 15 | ava@finance.com          |

Output

[
  {"email_domain":"alerts.finance.com","count":1},
  {"email_domain":"finance.com","count":2},
  {"email_domain":"mail.finance.com","count":1}
]

Explanation

Subdomains that end with '.com' are valid. A domain like 'ops.company' is excluded.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Emails <= 200000
Emails.id is unique within each test case
email is lowercase and represents a valid address format local@domain
The parsed domain is the substring after the first '@'
Return exactly: email_domain, count
Keep only domains ending with '.com'
Sort output by email_domain ASC
SQL submissions are evaluated on SQLite 3.27.2
Pandas submissions must return a DataFrame with columns [email_domain, count]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Emails =

[{"id":101,"email":"rina@outlook.com"},{"id":102,"email":"noah@yahoo.com"},{"id":103,"email":"amir@outlook.com"},{"id":104,"email":"lucy@research.org"},{"id":105,"email":"mia@example.net"},{"id":106,"email":"devon@analytics.yahoo.com"}]

Commercial Email Domain Census

Hints

Commercial Email Domain Census

Hints