Find duplicate email addresses

Easy

Question

The users table should have one account per email address, but people have signed up more than once.

Find every email address that belongs to more than one account. Treat upper and lower case as the same, so Asha@x.com and asha@x.com are the same address. Accounts with no email (NULL) are not duplicates of each other.

Return email (in lower case) and accounts (how many accounts use it). Sort by accounts from high to low, then by email from A to Z.

The order of the rows matters for this question.

group by
having
duplicates
null
lower

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.

users4 columns · 10 rowsnot run yet

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

Example rows of the users table
id	name	email	created_at
1	Asha	asha@mail.com	2026-01-02
2	Asha K	ASHA@mail.com	2026-01-09
3	Ravi	ravi@mail.com	2026-01-10
4	Meena	meena@mail.com	2026-01-11
5	Guest A	NULL	2026-01-12
6	Ravi S	Ravi@Mail.com	2026-01-13
7	Guest B	NULL	2026-01-14
8	Sam	sam@mail.com	2026-01-15
9	Asha Again	asha@mail.com	2026-01-16
10	Meena P	meena@mail.com	2026-01-17

About this SQL

Text comparison with = and GROUP BY is case-sensitive in SQLite (`a` and `A` are different), while MySQL is usually case-insensitive by default. Use LOWER() to be safe in all of them. GROUP BY puts all NULL values into one group, even though NULL = NULL is not true.

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