Sign-ups per email domain

Easy

Question

The customers table keeps the email that each person typed. People type carelessly: some emails have spaces at the start or end, and some have capital letters.

The domain of an email is the text after the @ sign. Count the customers for each domain. Write the domain in lower case and without spaces at either end, so MailHub.com and mailhub.com are the same domain. Leave out customers whose email is empty (NULL) or has no @ sign at all.

Return domain and signups (how many customers use the domain). Sort by signups from high to low, then by domain from A to Z.

The order of the rows matters for this question.

string
substr
instr
trim
lower
group by
null

Read first: Lesson 45, SQL groups: GROUP BY, HAVING and conditional counts

Tables

1 table

These are the example rows. Run uses only these rows. Submit also tests your query on a second set of rows that you cannot see.

customers3 columns · 10 rowsnot run yet

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, email TEXT);

Example rows of the customers table
id	name	email
1	Asha	asha@mailhub.com
2	Ravi	ravi@PostBox.com
3	Meena	MEENA@MAILHUB.COM
4	Sam	sam@quickmail.in
5	Guest	NULL
6	Tara	tara@postbox.com
7	Umesh	umesh@mailhub.com
8	Vani	vani-at-mail
9	Wasim	wasim@notmailhub.com
10	Kabir	kabir@mailhub.com

About this SQL

INSTR(text, '@') gives the position of the first @ (the first character is position 1) and gives 0 when the text has no @. SUBSTR(text, n) returns the text from position n to the end. SQLite has no SPLIT_PART and no RIGHT-of-a-character helper, so you build the cut with these two. TRIM removes spaces at both ends, and LOWER makes ASCII letters small. INSTR of NULL is NULL.

SQLite

Opening the code editor…

Result

Run shows what your query gives on the example rows.Submit checks it on the example rows and on a hidden set of rows.

SQL · ready