Find Gmail Customers — SQL — InterviewPrepKit

Solving tips

Use LIKE with the % wildcard to match a substring at the start or end of a value.
Anchor the pattern carefully: '%@gmail.com' matches the domain exactly, while '%gmail%' would over-match.
% matches any run of characters and _ matches exactly one character.

You are given a customers table and need to identify everyone using a Gmail address for a targeted email campaign.

Schema

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

Sample data:

id	name	email	city
1	Alice Smith	[email protected]	Boston
2	Bob Jones	[email protected]	Denver
3	Carol White	[email protected]	Austin
4	Dan Brown	[email protected]	Boston
5	Erin Black	[email protected]	Seattle
6	Frank Green	[email protected]	Miami

Task

Return the id, name, and email of every customer whose email ends with the domain @gmail.com. Order the rows by id in ascending order.

Expected output

id	name	email
1	Alice Smith	[email protected]
3	Carol White	[email protected]
5	Erin Black	[email protected]

Approach

Substring matching on text is what LIKE is for. Match the literal domain suffix with the pattern '%@gmail.com': the leading % allows any local part before the @, and the rest of the pattern pins the domain exactly so look-alike domains are rejected.

Query

SELECT id, name, email
FROM customers
WHERE email LIKE '%@gmail.com'
ORDER BY id ASC;

Walkthrough

The pattern requires the string to end in @gmail.com. Alice ([email protected]), Carol ([email protected]), and Erin ([email protected]) all satisfy it. Bob (@yahoo.com) and Dan (@company.org) fail on the domain. Frank’s [email protected] is the trap: it contains the letters gmail but does not end in @gmail.com, so the anchored pattern correctly excludes him. Ordering by id yields rows 1, 3, and 5.

Complexity & notes

A pattern with a leading % cannot use a standard B-tree index, so this is a sequential scan; a trigram (pg_trgm) index or a functional index on a reversed/extracted domain would help at scale. Use ILIKE in PostgreSQL for case-insensitive matching. The common pitfall is under-anchoring: LIKE '%gmail%' would wrongly match [email protected] and even [email protected], which is why the @ and the trailing .com are part of the pattern.