Keyword-Based User Search

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

We're running a search-quality audit and need the shoppers who searched for the hardware itself: a desk, a monitor, a cable, or a mouse. Return the unique user IDs, but leave out the accessory searches such as desk mats, monitor arms, and cable-management trays.

The search terms reveal intent.

Tables

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

What this really is

This is a substring-containment filter wearing a product-intent costume. The real question: do you remember that LIKE '%desk%' matches the letters 'desk' wherever they sit, so it grabs 'desk mat' right alongside 'standing desk'? Anyone can write the four OR LIKE '%kw%' conditions. What separates candidates is subtracting the accessory rows that share a product word, and keeping that subtraction from binding to the wrong term. Miss it and every accessory shopper, exactly the cohort the audit was told to drop, lands in your unique-user list.

Break down the requirements

Step 1: Build the inclusion OR chain

Join four search_term LIKE '%kw%' conditions with OR. Containment is the point: 'usb-c cable 6ft' and 'gaming mouse' never equal a bare product word, so they only match through the wildcard.

Step 2: Parenthesize the OR group

The accessory exclusion is an AND against the whole inclusion group. Without the parentheses, AND binds tighter than OR, so the exclusions guard only the mouse searches and 'desk mat' and 'monitor arm' walk straight back in.

Step 3: Subtract the accessory terms

'desk mat', 'monitor arm' and 'cable management' each contain a product word, so they satisfy a positive pattern and must be removed by hand: NOT LIKE '%mat%', NOT LIKE '%arm%', NOT LIKE '%management%'. This is the load-bearing step. On this seed each of those terms belongs to a different set of users, so every exclusion changes the answer.

Step 4: Collapse to unique users

Use SELECT DISTINCT user_id. A shopper shows up on several matching rows, and the audit wants the set of users, not the list of queries. Anonymous rows where user_id is NULL stay, because nothing filters them out.

The solution

SELECT DISTINCT user_id
FROM search_queries
WHERE (
        search_term LIKE '%desk%'
     OR search_term LIKE '%monitor%'
     OR search_term LIKE '%cable%'
     OR search_term LIKE '%mouse%'
      )
  AND search_term NOT LIKE '%mat%'
  AND search_term NOT LIKE '%arm%'
  AND search_term NOT LIKE '%management%'

Positive-only filter

Four LIKE conditions joined with OR. It matches 'standing desk' and also 'desk mat', 'monitor arm' and 'cable management', so the accessory shoppers are returned as hardware shoppers.

With accessory exclusions

The same OR group in parentheses, then AND ... NOT LIKE on '%mat%', '%arm%' and '%management%'. Only the shoppers who searched for the product itself remain.

Common follow-up questions

Common follow-up questions

• Why not `WHERE search_term IN ('desk','monitor','cable','mouse')`? (Tests whether they know `IN` is exact equality, not substring containment.)
• How do you keep `NOT LIKE '%arm%'` from also dropping 'warm blanket' or 'alarm clock'? (Probes word-boundary matching or a move to tokenized search.)
• Search terms start arriving in mixed case from a new client. What changes? (Tests whether they know `LIKE` case sensitivity differs by engine and normalise the column once.)
• What if the audit also needs the count of matching searches per user? (Forces a switch from `SELECT DISTINCT` to `GROUP BY user_id` with `COUNT(*)`.)
• Could you push the product-vs-accessory rule into the data pipeline instead of the query? (Tests whether they see it as a classification computed once upstream.)