# Not From Around Here > The data is mixed. Only some of it belongs. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The security team flagged sign-ups on the '@example.com' domain after a burst of suspicious activity. Pull every user whose email address sits on that domain, returning their user_id, username, and full email. ## 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. The separator that decides who passes the interview is the '@' in the pattern: leave it out and you quietly let in addresses that merely happen to contain the letters 'example.com' somewhere in the local part 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 the letters 'example.com'. 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, and it is what makes the match exact. **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: Give the list a stable order ORDER BY user_id makes the result deterministic and easy to eyeball. Without it the engine may hand back rows in any physical order, which makes two runs look different for no real reason. **Domain filter** ```sql SELECT user_id, username, email FROM users WHERE email LIKE '%@example.com' ORDER BY user_id ``` > **Common pitfall** > > Reaching for exact equality (email = '@example.com') returns zero rows: the column holds the WHOLE address, not just the domain. A suffix match, not an equality test, is what this needs. > **Interviewers watch for** > > The tell of a careful candidate is the '@' inside the pattern and a word about why it matters. Say out loud that '%example.com' would leak lookalike domains, and you have shown you think about false positives, not just happy-path matches. > **Performance insight** > > A leading-wildcard LIKE ('%...') cannot use a normal B-tree index on email, so this is a full scan on 10M rows. That is expected here. If domain filtering were a hot, repeated query, you would store the domain as its own column (or a functional index on the substring after '@') and match it with equality, which an index can serve. --- ## 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 LOWER('%@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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/not_from_around_here) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.