Sign-Ups by Email Provider

Medium
437.

Sign-Ups by Email Provider

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

ColumnType
user_idint
emailvarchar
signed_up_ondate

Primary key: user_id.

One row per account.

Example

The rows must come back in the order shown.

users

user_idemailsigned_up_on
1aarav.k@gmail.com2024-01-14
2diya92@googlemail.com2024-02-03
3kabir@outlook.com2024-03-21
4meera.n@iitb.ac.in2024-04-09
5rohan@outlook.com2024-06-30
6zara.s@gmail.com2023-12-31
7ishaan@iitb.ac.in2025-01-02
8priya.r@yahoo.co.in2024-11-11

Output

domainsignups
gmail.com2
outlook.com2
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.

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

ColumnType
user_idint
emailvarchar
signed_up_ondate

Primary key: user_id.

One row per account.

Examples

Example 1

users

user_idemailsigned_up_on
1aarav.k@gmail.com2024-01-14
2diya92@googlemail.com2024-02-03
3kabir@outlook.com2024-03-21
4meera.n@iitb.ac.in2024-04-09
5rohan@outlook.com2024-06-30
6zara.s@gmail.com2023-12-31
7ishaan@iitb.ac.in2025-01-02
8priya.r@yahoo.co.in2024-11-11

Output

domainsignups
gmail.com2
outlook.com2

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

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