Table accounts(id int primary key, email text).
E-mail addresses are case-insensitive. Return every address (in lower case) that belongs to more than one account, with the number of accounts as accounts. Order by accounts descending, then the address.
Constraints
- Write one SELECT statement for PostgreSQL 16.
- Output columns and row order must match exactly.
Input and output
- Input
- The setup script in the test case creates and fills the tables described above.
- Output
- Columns: email, accounts.
Example
Input
create table accounts (id int primary key, email text not null);
insert into accounts values (1,'ravi@mail.in'),(2,'RAVI@mail.in'),(3,'gita@mail.in'),(4,'Ravi@Mail.in'),(5,'omar@mail.in'),(6,'omar@mail.in');Output
ravi@mail.in|3
omar@mail.in|2ravi@mail.in appears three times in different cases; omar twice; gita once.
Show hints
- Normalise with lower() before grouping.
- HAVING count(*) > 1 keeps only the duplicates.
Show the explanation
Group on lower(email) so Ravi1@Mail.in and ravi1@mail.in land in the same group, then keep groups with count(*) > 1. To stop duplicates from being created in the first place, add a unique index on the expression: create unique index on accounts (lower(email)); (or use the citext type).
Original UniCircle question. Last reviewed 22 Oct 2026.