Learner Sign-ups by E-mail Domain

Easy
127.

Learner Sign-ups by E-mail Domain

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

ColumnType
learner_idint
full_namevarchar
emailvarchar
signed_up_ondate

Primary key: learner_id.

E-mail addresses are stored in lower case.

Example

The rows must come back in the order shown.

Learner

learner_idfull_nameemailsigned_up_on
1Aarav Sharmaaarav.s@gmail.com2024-01-04
2Diya Iyerdiya.iyer@iitb.ac.in2024-01-09
3Kabir Khankabir_k@gmail.com2024-02-11
4Meera Nairmeera@nitk.edu.in2024-02-20
5Rohan Dasrohan.das@iitb.ac.in2024-03-02
6Sneha Raosneha.r@outlook.com2024-03-15
7Zara Patelzara.patel@gmail.com2024-03-18

Output

domainlearners
gmail.com3
iitb.ac.in2
nitk.edu.in1
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.

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

ColumnType
learner_idint
full_namevarchar
emailvarchar
signed_up_ondate

Primary key: learner_id.

E-mail addresses are stored in lower case.

Examples

Example 1

Learner

learner_idfull_nameemailsigned_up_on
1Aarav Sharmaaarav.s@gmail.com2024-01-04
2Diya Iyerdiya.iyer@iitb.ac.in2024-01-09
3Kabir Khankabir_k@gmail.com2024-02-11
4Meera Nairmeera@nitk.edu.in2024-02-20
5Rohan Dasrohan.das@iitb.ac.in2024-03-02
6Sneha Raosneha.r@outlook.com2024-03-15
7Zara Patelzara.patel@gmail.com2024-03-18

Output

domainlearners
gmail.com3
iitb.ac.in2
nitk.edu.in1
outlook.com1

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

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

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