Commercial Email Domain Census

Easy

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 1

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 2

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 3

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.

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]

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 |

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 |