Level 4 · Find Gmail customers

= needs the whole value to match. LIKE matches a pattern:

wildcard	matches
%	any number of characters, including none
_	exactly one character

WHERE name LIKE '%Lamp%'      -- contains "Lamp"
WHERE name LIKE 'Oak%'        -- starts with "Oak"
WHERE email LIKE '%@gmail.com' -- ends with "@gmail.com"

Without any wildcard, LIKE behaves like =.

Case sensitivity differs by database

• Postgres, DuckDB, BigQuery: LIKE is case-sensitive. 'Megan@Gmail.com' LIKE '%@gmail.com' is false.
• MySQL: case-insensitive by default (it depends on the column’s collation).
• Postgres and DuckDB also have ILIKE, a case-insensitive LIKE. MySQL and BigQuery do not have it.

The portable solution is to lowercase the column and write the pattern in lowercase:

WHERE LOWER(email) LIKE '%@gmail.com'

This filters with the lowercased value but still returns email exactly as stored, because the SELECT list uses the original column.

Why this matters

Customer-entered text is messy. Harbor & Pine’s data has emails with capitals and city names with odd casing and spaces, just like real CRMs. If you filter with a case-sensitive pattern you undercount, and nobody notices because the query did not error. A good habit: when a text filter is part of a number someone will act on, compare the count with and without LOWER(). If they differ, mention it.

On this dataset the difference is real: a case-sensitive email LIKE '%@gmail.com' misses one customer whose address was typed in capitals. One row sounds trivial, but the same habit on a million-row table misses thousands.

Leading wildcards are slow

LIKE 'Oak%' can use an index on a sorted column. LIKE '%lamp%' cannot, because the match could start anywhere, so the database scans every row. On a 2,000-row table you will not notice. On a billion-row event table you will. Columnar engines like DuckDB and BigQuery scan anyway, so it matters less there.

Common mistakes

• Forgetting the %: LIKE '@gmail.com' only matches the exact string @gmail.com.
• Matching too loosely: LIKE '%gmail%' also catches notgmail.co. Anchor the pattern as tightly as the question allows.
• Searching for a literal % or _. They need escaping, for example LIKE '%10\%%' ESCAPE '\'.