Hotel Loyalty Members per Email Domain

Easy
528.

Hotel Loyalty Members per Email Domain

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

ColumnType
member_idint
full_namevarchar
emailvarchar
joined_ondate

Primary key: member_id.

One row per member. Every email is stored in lower case and contains exactly one @.

Example

The rows must come back in the order shown.

LoyaltyMember

member_idfull_nameemailjoined_on
1Aditi Sharmaaditi.sharma@gmail.com2023-04-11
2Rahul Iyerrahul.iyer@corp.tcs.com2023-06-02
3Priya Nairpriyan@gmail.com2023-09-19
4Karan Mehtakaran.m@infosys.com2024-01-07
5Saanvi Raosaanvi.rao@corp.tcs.com2024-02-25
6Dev Joshidevj@outlook.com2024-05-30
7Ira Boseira.bose@gmail.com2024-08-14

Output

domainmembers
gmail.com3
corp.tcs.com2
infosys.com1
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.

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

ColumnType
member_idint
full_namevarchar
emailvarchar
joined_ondate

Primary key: member_id.

One row per member. Every email is stored in lower case and contains exactly one @.

Examples

Example 1

LoyaltyMember

member_idfull_nameemailjoined_on
1Aditi Sharmaaditi.sharma@gmail.com2023-04-11
2Rahul Iyerrahul.iyer@corp.tcs.com2023-06-02
3Priya Nairpriyan@gmail.com2023-09-19
4Karan Mehtakaran.m@infosys.com2024-01-07
5Saanvi Raosaanvi.rao@corp.tcs.com2024-02-25
6Dev Joshidevj@outlook.com2024-05-30
7Ira Boseira.bose@gmail.com2024-08-14

Output

domainmembers
gmail.com3
corp.tcs.com2
infosys.com1
outlook.com1

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, domain

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