NGO Donors Grouped by Email Domain

Easy
76.

NGO Donors Grouped by Email Domain

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

ColumnType
donor_idint
full_namevarchar
emailvarchar
cityvarchar

Primary key: donor_id.

One row per donor. email has exactly one @, or is NULL.

Example

The rows must come back in the order shown.

donors

donor_idfull_nameemailcity
1Ananya Raoananya.rao@gmail.comBengaluru
2Vikram Singhvikram_s@yahoo.co.inDelhi
3Neha Guptaneha91@gmail.comPune
4Farhan KhanNULLMumbai
5Pooja Menonpooja@akshayfoundation.orgKochi
6Dev Pateldevp@yahoo.co.inAhmedabad
7Ira Boseira.bose@outlook.comKolkata

Output

email_domaindonors
gmail.com2
yahoo.co.in2
akshayfoundation.org1
outlook.com1
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
donor_idint
full_namevarchar
emailvarchar
cityvarchar

Primary key: donor_id.

One row per donor. email has exactly one @, or is NULL.

Examples

Example 1

donors

donor_idfull_nameemailcity
1Ananya Raoananya.rao@gmail.comBengaluru
2Vikram Singhvikram_s@yahoo.co.inDelhi
3Neha Guptaneha91@gmail.comPune
4Farhan KhanNULLMumbai
5Pooja Menonpooja@akshayfoundation.orgKochi
6Dev Pateldevp@yahoo.co.inAhmedabad
7Ira Boseira.bose@outlook.comKolkata

Output

email_domaindonors
gmail.com2
yahoo.co.in2
akshayfoundation.org1
outlook.com1

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_domain

Another 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_domain

Another 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 →