Searches by Users With Email

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

ESPN

Problem

The search quality team is analyzing result effectiveness but only for identifiable users. Pull all search query details for users who have an email address on file, limited to queries that returned at least one result.

One user's search behavior.

Tables

search_queries

Columns: query_id INTEGER, user_id INTEGER, search_term TEXT, results_count INTEGER, clicked_result INTEGER, query_time TEXT

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

This is a two-part identity filter dressed up as a search-quality report. The join to users feels like it already means 'users who have an email on file', and that feeling is the trap. An inner join matches on user_id, not on email, so a user row whose email is NULL joins just as happily as one with an address. Anyone can write the join. What separates candidates is the explicit u.email IS NOT NULL that the join cannot express, plus the results_count > 0 gate. Skip the email test and every search from an emailless user leaks into a report that was meant to hold only identifiable people.

Step 1: Join search_queries to users on user_id

A search row carries no email, so the join is the only way to see one. As a bonus, an inner join drops the anonymous searches whose user_id is NULL, because NULL never equals anything. That is the business rule (no person, not identifiable), so reaching for a LEFT JOIN here would be wrong.

Step 2: Test email explicitly

A matched user row is not proof of an address. On this data a slice of users have a NULL email, and they own searches. Only u.email IS NOT NULL removes them, and no join condition on user_id can do that job.

Step 3: Keep searches that returned something

sq.results_count > 0 keeps searches with at least one result. It also quietly drops rows where results_count is NULL, since a comparison with NULL is never true. That is the right reading: an unknown count is not evidence that results came back.

Step 4: Return the search columns only

The ask is search query details, so project the six search_queries columns with the sq. prefix. Leaving email or username out keeps the output in the shape the preview shows and avoids an ambiguous user_id.

SELECT sq.query_id, sq.user_id, sq.search_term, sq.results_count, sq.clicked_result, sq.query_time
FROM search_queries sq
JOIN users u ON sq.user_id = u.user_id
WHERE u.email IS NOT NULL AND sq.results_count > 0

Join alone

JOIN users u ON sq.user_id = u.user_id keeps every search whose user exists, including users with a NULL email. The row count looks plausible, which is why nobody notices.

Join plus the email test

Adding u.email IS NOT NULL removes exactly the emailless users. The result shrinks to people the team can actually identify, which is the whole point of the report.

Common follow-up questions

• What changes if a missing email is sometimes stored as an empty string instead of NULL? (Tests whether the candidate knows `IS NOT NULL` does not catch '' and would add `NULLIF` or a length check.)
• If `users` could hold two rows for one `user_id`, what would happen to this output? (Tests awareness of join fan-out duplicating search rows.)
• How would you turn this into a count of qualifying searches per user? (Tests moving from a row filter to an aggregate without losing the NULL handling.)