SQL Extract an Email Domain
A specific, common application of SPLIT_PART — pulling out just the company/provider portion of an email address.
What & Why
Grouping users by email domain — a common way to spot company-wide signups, or filter out free providers like gmail.com — needs exactly the SPLIT_PART(email, '@', 2) pattern from earlier in this series, applied directly.
See How It Works
BUSINESS QUESTION
Marketing wants lead counts by normalized email domain.
| id | campaign_id | created_at | qualified_at | converted_at | lead_score | source | country | |
|---|---|---|---|---|---|---|---|---|
| 301 | 1 | ana@example.com | 2024-01-21 09:10:00+00 | 2024-01-22 11:00:00+00 | 2024-02-02 10:00:00+00 | 86 | google_ads | US |
| 302 | 1 | ben@example.com | 2024-01-24 12:40:00+00 | NULL | NULL | 52 | google_ads | CA |
| 303 | 2 | chloe@example.com | 2024-02-16 08:30:00+00 | 2024-02-18 14:20:00+00 | NULL | 74 | GB | |
| 304 | 3 | dev@example.com | 2024-03-20 17:15:00+00 | 2024-03-21 09:00:00+00 | 2024-04-04 16:00:00+00 | 91 | US |
EXAMPLE QUERY
SELECT
LOWER(SPLIT_PART(email, '@', 2)) AS email_domain,
COUNT(*) AS leads
FROM marketing.leads
WHERE POSITION('@' IN email) > 1
GROUP BY email_domain
ORDER BY leads DESC, email_domain;RESULT — exact output from the displayed Queryflo rows
| email_domain | leads |
|---|---|
| example.com | 4 |
All four valid displayed lead emails share the example.com domain.
Now You Try
Practice this concept
Marketing wants lead counts by normalized email domain, excluding values without an at sign.
Available schema
marketingPrefix tables with marketing.table_name.
email_domainleadsmarketing.leads| Column | Type |
|---|---|
| id | integer |
| campaign_id | integer |
| text | |
| created_at | timestamp with time zone |
| qualified_at | timestamp with time zone |
| converted_at | timestamp with time zone |
| lead_score | integer |
| source | text |
| country | text |
| archive_status | text |
query.sql
Sign up free to try it on a real business scenario