Not From Around Here

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

PwC

Problem

List every user whose email address sits on the '@example.com' domain, with their user_id, username, and full email, for the security team tracing a burst of suspicious sign-ups on that domain. The team works through the accounts by user_id, lowest first.

The data is mixed. Only some of it belongs.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this really is

Strip away the security framing and this is a suffix match on a string. The skill being probed is whether you can pin the '@example.com' domain to the END of the email and nowhere else. Everyone reaches for LIKE. What decides who passes the interview is the '@' in the pattern: leave it out and you quietly let in addresses that merely happen to end in the letters 'example.com', or a lookalike subdomain.

The trap

The domain lives at the tail of the string, so the wildcard belongs on the LEFT of the literal: '%@example.com'. Two near-misses cost candidates the round. Writing '%example.com' (no '@') matches 'boss@notexample.com' and 'ceo@sub.example.com' because both end in those letters. Writing '@example.com%' anchors to the wrong side and matches nothing useful. The '@' is not decoration; it is the boundary between the local part and the domain.

Over-matches (wrong)

email LIKE '%example.com' also returns 'joe@notexample.com' and 'x@mail.example.com'. The security team gets a polluted list and chases the wrong accounts.

Exact domain (right)

email LIKE '%@example.com' returns only addresses whose domain is precisely example.com. The '@' anchors the domain boundary.

Building the query

Step 1: Filter to the flagged domain

Put the wildcard before the literal so the pattern floats over any local part but locks '@example.com' to the end: WHERE email LIKE '%@example.com'. The leading '%' absorbs 'alice', 'aaron42', anything before the '@'.

Step 2: Return only the three requested columns

Select user_id, username and email. No SELECT *: the audit wants exactly those fields, and naming them keeps the output stable if the table grows more columns later.

Step 3: Hand the list back by user_id

The team works the list by user_id, lowest first, and that sequence is graded. ORDER BY user_id is total here because user_id is unique, so two correct answers can never disagree on which row comes next.

SELECT user_id, username, email
FROM users
WHERE email LIKE '%@example.com'
ORDER BY user_id

Common follow-up questions

• Some rows have a `NULL` email. Does your `WHERE` clause include or exclude them, and is that what the audit wants? (Tests understanding that `LIKE` against `NULL` yields UNKNOWN, so `NULL` emails are silently dropped.)
• How would you make the match case-insensitive so 'Alice@Example.com' still qualifies? (Tests knowledge of collation and `LOWER(email) LIKE '%@example.com'`.)
• The team now wants everyone on example.com OR any of its subdomains like 'mail.example.com'. How does your pattern change? (Tests reasoning about '%@%example.com' versus splitting on '@' and matching the domain suffix precisely.)
• This runs as a full scan on 10M rows. If it had to run every minute, what would you change in the schema? (Tests whether the candidate can move from a leading-wildcard scan to an indexable equality on a derived domain column.)