Sign-Ups by Email Provider — SQL Strings Problem
Growth wants to know which email providers people sign up with.
- Difficulty: Medium
- Topics: Strings, Aggregation, Conditional Logic
- Dialect: MySQL
- Problem: #437
Problem statement
Growth wants to know which email providers people sign up with. The provider is the part of the email after the @, except that googlemail.com is the same provider as gmail.com and must be counted as gmail.com.
Considering only accounts that signed up in 2024, return the columns domain and signups for providers with at least 2 sign-ups, ordered by signups descending, then domain. Emails are stored lower-case and contain exactly one @.
Tables
Table: users
| Column | Type |
|---|---|
| user_id | int |
| varchar | |
| signed_up_on | date |
Primary key: user_id.
One row per account.
Examples
Example 1
users
| user_id | signed_up_on | |
|---|---|---|
| 1 | aarav.k@gmail.com | 2024-01-14 |
| 2 | diya92@googlemail.com | 2024-02-03 |
| 3 | kabir@outlook.com | 2024-03-21 |
| 4 | meera.n@iitb.ac.in | 2024-04-09 |
| 5 | rohan@outlook.com | 2024-06-30 |
| 6 | zara.s@gmail.com | 2023-12-31 |
| 7 | ishaan@iitb.ac.in | 2025-01-02 |
| 8 | priya.r@yahoo.co.in | 2024-11-11 |
Output
| domain | signups |
|---|---|
| gmail.com | 2 |
| outlook.com | 2 |
How to solve Sign-Ups by Email Provider
Extract the provider with SUBSTRING_INDEX(email, '@', -1) — a negative count takes the part after the last delimiter — or with SUBSTRING(email, LOCATE('@', email) + 1). Then normalise: a CASE maps googlemail.com to gmail.com (the alternative uses REPLACE, which is safe because no other provider's name contains that text).
The merge must happen before the GROUP BY. Grouping by the raw domain and fixing the label afterwards would leave two groups both called gmail.com. Computing the clean domain in a derived table and grouping the outer query by it is the tidy way; grouping directly by the full expression, as the alternative does, works too.
The year filter keeps the column bare with BETWEEN on the date, and HAVING COUNT(*) >= 2 drops one-off providers. Ties on the count fall back to the domain name. It is one scan plus a small grouping.
Reference solution (MySQL)
SELECT domain, COUNT(*) AS signups
FROM (
SELECT CASE WHEN SUBSTRING_INDEX(email, '@', -1) = 'googlemail.com' THEN 'gmail.com'
ELSE SUBSTRING_INDEX(email, '@', -1) END AS domain
FROM users
WHERE signed_up_on BETWEEN '2024-01-01' AND '2024-12-31'
) t
GROUP BY domain
HAVING COUNT(*) >= 2
ORDER BY signups DESC, domainAnother way
SELECT REPLACE(SUBSTRING(email, LOCATE('@', email) + 1), 'googlemail.com', 'gmail.com') AS domain, COUNT(*) AS signups
FROM users WHERE YEAR(signed_up_on) = 2024
GROUP BY REPLACE(SUBSTRING(email, LOCATE('@', email) + 1), 'googlemail.com', 'gmail.com')
HAVING COUNT(*) >= 2 ORDER BY signups DESC, domain← Sharp Follower Drops Between Daily Snapshots · Most Common Leading Hashtags in Captions →