NGO Donors Grouped by Email Domain — SQL Strings Problem
An NGO's fundraising team wants to know which mail providers its donors use before choosing a newsletter service.
- Difficulty: Easy
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #76
Problem statement
An NGO's fundraising team wants to know which mail providers its donors use before choosing a newsletter service. Every stored email is lower-case; some donors gave no email (NULL).
Return each domain — the part of the email after the @ — in a column email_domain, with donors, the number of donors using it. Ignore donors without an email. Order by donors descending, then email_domain ascending.
Tables
Table: donors
| Column | Type |
|---|---|
| donor_id | int |
| full_name | varchar |
| varchar | |
| city | varchar |
Primary key: donor_id.
One row per donor. email has exactly one @, or is NULL.
Examples
Example 1
donors
| donor_id | full_name | city | |
|---|---|---|---|
| 1 | Ananya Rao | ananya.rao@gmail.com | Bengaluru |
| 2 | Vikram Singh | vikram_s@yahoo.co.in | Delhi |
| 3 | Neha Gupta | neha91@gmail.com | Pune |
| 4 | Farhan Khan | NULL | Mumbai |
| 5 | Pooja Menon | pooja@akshayfoundation.org | Kochi |
| 6 | Dev Patel | devp@yahoo.co.in | Ahmedabad |
| 7 | Ira Bose | ira.bose@outlook.com | Kolkata |
Output
| email_domain | donors |
|---|---|
| gmail.com | 2 |
| yahoo.co.in | 2 |
| akshayfoundation.org | 1 |
| outlook.com | 1 |
How to solve NGO Donors Grouped by Email Domain
The grouping key is not a column but an expression: the domain cut out of the email. SUBSTRING_INDEX(email, '@', -1) keeps everything to the right of the last @; with exactly one @ per address that is the domain. The same piece can be had with SUBSTRING(email, LOCATE('@', email) + 1) or RIGHT(email, CHAR_LENGTH(email) - INSTR(email, '@')).
Donors without an email must not form a NULL group. Filtering WHERE email IS NOT NULL drops them; the second alternative instead lets the NULL group form, counts COUNT(email) (which ignores NULLs, giving 0) and removes it with HAVING.
Emails are stored lower-case, so gmail.com never appears under two spellings — if they were not, you would wrap the expression in LOWER(). Ties between domains are broken by the domain name. The whole query is one scan plus a group by on short strings.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(email, '@', -1) AS email_domain, COUNT(*) AS donors
FROM donors
WHERE email IS NOT NULL
GROUP BY SUBSTRING_INDEX(email, '@', -1)
ORDER BY donors DESC, email_domainAnother way
SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS email_domain, COUNT(email) AS donors FROM donors GROUP BY SUBSTRING(email, LOCATE('@', email) + 1) HAVING COUNT(email) > 0 ORDER BY donors DESC, email_domainAnother way
SELECT email_domain, COUNT(*) AS donors FROM (SELECT RIGHT(email, CHAR_LENGTH(email) - INSTR(email, '@')) AS email_domain FROM donors WHERE email IS NOT NULL) d GROUP BY email_domain ORDER BY donors DESC, email_domain← Concerts Without a Single Sold Ticket · Polling Booth Turnout Bands →