Signups by Work Email Domain — SQL Strings Problem
Sales wants to spot companies where several employees signed up on their own — a sign the team is ready for a business plan.
- Difficulty: Easy
- Topics: Strings, Aggregation
- Dialect: MySQL
- Problem: #401
Problem statement
Sales wants to spot companies where several employees signed up on their own — a sign the team is ready for a business plan. Free mailboxes say nothing about the company, so signups at gmail.com, yahoo.com, outlook.com and rediffmail.com are ignored.
Return each remaining email domain (the part after @) with at least two signups, in the columns domain and signups, ordered by signups descending, then domain ascending. Every email is lower-case and has exactly one @.
Tables
Table: signups
| Column | Type |
|---|---|
| user_id | int |
| varchar | |
| signed_up_on | date |
Primary key: user_id.
One row per self-serve signup.
Examples
Example 1
signups
| user_id | signed_up_on | |
|---|---|---|
| 1 | priya@quillpad.in | 2024-04-01 |
| 2 | rahul@gmail.com | 2024-04-01 |
| 3 | sneha@quillpad.in | 2024-04-03 |
| 4 | dev@vaani.ai | 2024-04-04 |
| 5 | zara@gmail.com | 2024-04-05 |
| 6 | ira@ledgerly.com | 2024-04-05 |
| 7 | karan@ledgerly.com | 2024-04-07 |
| 8 | tanvi@quillpad.in | 2024-04-08 |
Output
| domain | signups |
|---|---|
| quillpad.in | 3 |
| ledgerly.com | 2 |
How to solve Signups by Work Email Domain
Each signup is reduced to its domain, and the domains are counted. SUBSTRING_INDEX(email, '@', -1) returns everything after the last @, which with exactly one @ is the domain. SUBSTRING(email, LOCATE('@', email) + 1) gets the same text by position.
Removing the free mailboxes in WHERE drops those rows before grouping, so the database never builds a gmail.com group only to discard it; doing it in HAVING on the grouped domain gives the same answer. The "at least two" condition is about the group's size, so it can only be in HAVING.
The order has two keys because counts tie: two domains with two signups each are ordered alphabetically. The query is one scan and a hash on the domain. In a real table the emails would be stored lower-case precisely so a grouping like this never splits Quillpad.in from quillpad.in.
Reference solution (MySQL)
SELECT SUBSTRING_INDEX(email, '@', -1) AS domain, COUNT(*) AS signups
FROM signups
WHERE SUBSTRING_INDEX(email, '@', -1) NOT IN ('gmail.com', 'yahoo.com', 'outlook.com', 'rediffmail.com')
GROUP BY SUBSTRING_INDEX(email, '@', -1)
HAVING COUNT(*) >= 2
ORDER BY signups DESC, domainAnother way
SELECT domain, COUNT(*) AS signups FROM (SELECT SUBSTRING(email, LOCATE('@', email) + 1) AS domain FROM signups) d WHERE domain NOT IN ('gmail.com', 'yahoo.com', 'outlook.com', 'rediffmail.com') GROUP BY domain HAVING COUNT(*) >= 2 ORDER BY signups DESC, domainAnother way
SELECT RIGHT(email, CHAR_LENGTH(email) - INSTR(email, '@')) AS domain, COUNT(*) AS signups FROM signups GROUP BY RIGHT(email, CHAR_LENGTH(email) - INSTR(email, '@')) HAVING COUNT(*) >= 2 AND domain NOT IN ('gmail.com', 'yahoo.com', 'outlook.com', 'rediffmail.com') ORDER BY signups DESC, domain← Daily Active Users From the Event Log · Workspace Seat Utilisation Status →