Silent Users

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

Domain

SQL

Difficulty

medium

Seniority

L4

Problem

An account counts as silent only when not a single row in search_queries belongs to it. From users, list every silent account's username and signup date, most recently joined first.

Users who have never typed a query.

Tables

users

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

search_queries

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

Expected output

Worked solution and explanation

This is a set difference dressed up as an engagement audit: every account in users minus every account that appears in search_queries. Anyone can say that out loud. What separates candidates is noticing that search_queries.user_id is blank on a quarter of the rows (50 of 200 here). Write the difference as user_id NOT IN (SELECT user_id FROM search_queries) and a single NULL in that list makes every comparison unknown. The audit then comes back empty and reports full engagement, when in fact 125 accounts have never searched.

Three moves to the answer

Step 1: Keep every account with a LEFT JOIN

Start from users and left-join search_queries on user_id. Every account survives the join. An account with no searches comes through once, with every search_queries column NULL. An inner join would discard exactly the rows the audit is looking for.

Step 2: Keep only the rows the join could not match

Filter with WHERE sq.query_id IS NULL. query_id is never NULL in the table, so a NULL there can only mean the account had no match. If you test a nullable column like clicked_result instead, you also sweep in active users whose searches never got a click.

Step 3: Sort newest signup first

ORDER BY u.signup_date DESC is the only ordering the audit asks for. Two accounts that signed up on the same day can come back in either order.

SELECT u.username, u.signup_date
FROM users AS u
LEFT JOIN search_queries AS sq
  ON sq.user_id = u.user_id
WHERE sq.query_id IS NULL
ORDER BY u.signup_date DESC

The membership test

WHERE user_id NOT IN (SELECT user_id FROM search_queries) reads like the requirement word for word. But NOT IN means 'differs from every value in the list', and a comparison with NULL is never true. With 50 blank user_id values on file, it returns zero rows.

The unmatched-row filter

LEFT JOIN search_queries followed by WHERE sq.query_id IS NULL asks a different question: did this account match any search row at all? A blank user_id matches nobody, so it never affects the result, and all 125 silent accounts come back.

Common follow-up questions

• Silent now means no search in the last 90 days of `query_time`. Where does that condition go so that long-dormant users are still counted? (Tests whether the date filter goes into the join condition rather than the `WHERE`, which would quietly turn the query back into an inner join.)
• Rewrite it with `NOT EXISTS`. Does the NULL `user_id` problem come back? (Tests whether the candidate understands that a correlated `EXISTS` compares row by row and is NULL-safe.)
• Two accounts have a `signup_date` that includes a time of day. How would you sort by calendar day only? (Probes noticing mixed date and timestamp text in the same column.)