Who's Looking

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

Airbnb

Problem

Our search dashboard counts raw queries, so a term one person keeps re-running looks as popular as a term many people tried once. Using search_queries, find how many different users searched each term, widest reach first.

Every search is a question someone needed answered. Count the people asking.

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 problem is really about

This is a reach metric dressed up as a search dashboard. search_queries stores one row per query, and on this data every logged searcher shows up twice for the same term. A row tally of 'bluetooth speaker' says 10 when five people searched it. Anyone can bucket by term; what separates candidates is counting the people inside the bucket once each. Reach for COUNT(user_id) and every figure doubles. Reach for COUNT(*) and 'ergonomic chair', searched only anonymously, also reports ten searchers when the honest answer is 0.

Walking through it

Step 1: Bucket by search_term

One output row per term as it was logged, so search_term is the grouping key. The log keeps terms exactly as typed, which is why ' 4k monitor' with its leading space sits in its own row next to '4k monitor'.

Step 2: Count people, not searches

COUNT(DISTINCT user_id) folds a user's repeat searches into one and skips NULL user_id rows on its own. 'bluetooth speaker' has ten rows from five people, so its reach is 5. A term whose every row is anonymous keeps its bucket and reads 0; the ' wireless headphones' row in the preview is exactly that case.

Step 3: Sort by reach and stop there

Sort by unique_searchers descending. Many terms tie at 5 and nothing asks for an order among them, so a search_term tiebreaker is harmless but optional: rows that tie on reach may come in any order.

The solution

SELECT search_term,
       COUNT(DISTINCT user_id) AS unique_searchers
FROM search_queries
GROUP BY search_term
ORDER BY unique_searchers DESC

Counting searches

COUNT(user_id) counts every non-null row, so a user's repeat searches all add up. It reports 10 for 'bluetooth speaker'.

Counting searchers

COUNT(DISTINCT user_id) counts each person once per term. It reports 5 for 'bluetooth speaker', and that is the reach metric.

Common follow-up questions

• How would you show only terms searched by at least 100 different people? (Filter the aggregate with `HAVING COUNT(DISTINCT user_id) >= 100`, not a raw column.)
• What if you needed unique searchers per term per day? (Add a day bucket from `query_time` to the grouping key.)
• ' 4k monitor' and '4k monitor' land in separate rows. How would you merge spellings that differ only in spacing or case? (Group on a normalized key such as `LOWER(TRIM(search_term))` and decide which spelling to display.)
• How would this hold up at billions of rows in near real time? (Approximate distinct counting (HyperLogLog) when exact distinct sets get too expensive.)