Hotel Loyalty Members per Email Domain — SQL Strings Problem
The hotel chain's loyalty programme wants to know which email providers and corporate domains its members sign up with, to plan a corporate-rates campaign.
- Difficulty: Easy
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #528
Problem statement
The hotel chain's loyalty programme wants to know which email providers and corporate domains its members sign up with, to plan a corporate-rates campaign. The domain of an email address is everything after the @.
Return domain and members (how many members use it), ordered by members descending, then by domain ascending.
Tables
Table: LoyaltyMember
| Column | Type |
|---|---|
| member_id | int |
| full_name | varchar |
| varchar | |
| joined_on | date |
Primary key: member_id.
One row per member. Every email is stored in lower case and contains exactly one @.
Examples
Example 1
LoyaltyMember
| member_id | full_name | joined_on | |
|---|---|---|---|
| 1 | Aditi Sharma | aditi.sharma@gmail.com | 2023-04-11 |
| 2 | Rahul Iyer | rahul.iyer@corp.tcs.com | 2023-06-02 |
| 3 | Priya Nair | priyan@gmail.com | 2023-09-19 |
| 4 | Karan Mehta | karan.m@infosys.com | 2024-01-07 |
| 5 | Saanvi Rao | saanvi.rao@corp.tcs.com | 2024-02-25 |
| 6 | Dev Joshi | devj@outlook.com | 2024-05-30 |
| 7 | Ira Bose | ira.bose@gmail.com | 2024-08-14 |
Output
| domain | members |
|---|---|
| gmail.com | 3 |
| corp.tcs.com | 2 |
| infosys.com | 1 |
| outlook.com | 1 |
How to solve Hotel Loyalty Members per Email Domain
The work is in extracting the domain; after that it is a plain count per group. SUBSTRING_INDEX(email, '@', -1) returns everything after the last @ (a negative count counts delimiters from the right), which is the domain because each address has exactly one. Equivalently, LOCATE('@', email) finds the position of the @ and SUBSTRING(email, pos + 1) takes the rest.
Group by that expression — or compute it in a derived table and group by its alias — and COUNT(*) the members of each domain. The report is ordered, so the ORDER BY fixes both keys: the count descending, then the domain alphabetically so that two domains with the same count always come out in the same order.
Because the addresses are stored in lower case, Gmail.com and gmail.com can never split one provider into two groups; on real data you would wrap the expression in LOWER(). The query is one scan plus a sort of the (few) groups.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain, COUNT(*) AS members
FROM LoyaltyMember
GROUP BY SUBSTRING_INDEX(email, '@', -1)
ORDER BY members DESC, domainAnother way
SELECT domain, COUNT(*) AS members
FROM (SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain FROM LoyaltyMember) d
GROUP BY domain
ORDER BY COUNT(*) DESC, domain ASC← Current Status Labels for Train PNRs · Hotel Rooms Priced Above the Average Tariff →