Learner Sign-ups by E-mail Domain — SQL Strings Problem
The partnerships team of an online learning platform wants to know which colleges and mail providers its learners sign up from, to decide where to pitch…
- Difficulty: Easy
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #127
Problem statement
The partnerships team of an online learning platform wants to know which colleges and mail providers its learners sign up from, to decide where to pitch campus licences. The domain is everything after the @ in a learner's e-mail address.
Return one row per domain with the columns domain and learners (the number of learners using it). Every e-mail contains exactly one @. Order the rows by learners highest first, then by domain alphabetically.
Tables
Table: Learner
| Column | Type |
|---|---|
| learner_id | int |
| full_name | varchar |
| varchar | |
| signed_up_on | date |
Primary key: learner_id.
E-mail addresses are stored in lower case.
Examples
Example 1
Learner
| learner_id | full_name | signed_up_on | |
|---|---|---|---|
| 1 | Aarav Sharma | aarav.s@gmail.com | 2024-01-04 |
| 2 | Diya Iyer | diya.iyer@iitb.ac.in | 2024-01-09 |
| 3 | Kabir Khan | kabir_k@gmail.com | 2024-02-11 |
| 4 | Meera Nair | meera@nitk.edu.in | 2024-02-20 |
| 5 | Rohan Das | rohan.das@iitb.ac.in | 2024-03-02 |
| 6 | Sneha Rao | sneha.r@outlook.com | 2024-03-15 |
| 7 | Zara Patel | zara.patel@gmail.com | 2024-03-18 |
Output
| domain | learners |
|---|---|
| gmail.com | 3 |
| iitb.ac.in | 2 |
| nitk.edu.in | 1 |
| outlook.com | 1 |
How to solve Learner Sign-ups by E-mail Domain
The question has two steps: derive the domain from each address, then count addresses per domain. SUBSTRING_INDEX(email, '@', -1) returns the part of the string after the last occurrence of the delimiter — with a negative count it counts from the right — which, with exactly one @, is the domain.
Group by that expression and COUNT(*) the rows of each group. Order by the count descending and break ties alphabetically on the domain, as the statement asks, so the order is fully fixed.
The same domain can be cut with LOCATE('@', email) (the position of the @) and SUBSTRING from the next character, or with RIGHT over the length that remains. Computing it once in a derived table keeps the GROUP BY short. Either way the work is one scan plus a group-by on a short string.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain, COUNT(*) AS learners
FROM Learner
GROUP BY SUBSTRING_INDEX(email, '@', -1)
ORDER BY learners DESC, domainAnother way
SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain, COUNT(learner_id) AS learners FROM Learner GROUP BY SUBSTRING(email, LOCATE('@', email) + 1) ORDER BY 2 DESC, 1 ASCAnother way
SELECT domain, COUNT(*) AS learners FROM (SELECT RIGHT(email, CHAR_LENGTH(email) - INSTR(email, '@')) AS domain FROM Learner) t GROUP BY domain ORDER BY learners DESC, domain← Average Quiz Score for Quizzes With Three or More Attempts · Exam Marks to University Grade Letters →