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

The search quality team wants to know which search terms are reaching the widest audience. For each term, count how many different users searched it, with the 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 per-group unique count wearing an audience-reach costume. The skill being probed: can you separate searches from searchers? The table stores one row per query, so a term one obsessive user hit ten times has ten rows, while a term ten different people each searched once also has ten rows. Reach only cares about the second kind. The trap is reaching for COUNT(*) or COUNT(user_id): both count rows, so the repeat-searcher term looks just as broad as the genuinely popular one. DISTINCT is what collapses the same user back to one.

Walking through it

Step 1: Bucket by term

One output row per search term, so the term is the grouping key. Everything else is computed within each of those buckets.

Step 2: Count distinct searchers, not searches

Inside each bucket, COUNT(DISTINCT user_id) folds every repeated searcher down to one and silently drops the NULL user_id rows (anonymous searches), giving the true count of different people. Look at 'laptop deals' in the sample: user 100 appears twice but the reach is 3, not 4.

Step 3: Order by reach, break ties cleanly

Sort by the count descending so the widest-reach terms surface first. A secondary sort on search_term keeps ties (here gaming mouse and smart watch both at 2) in a stable, predictable 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, search_term

COUNT(`user_id`)

Counts rows, so repeat searches by one person inflate the number. Reports 4 for 'laptop deals'.

COUNT(DISTINCT `user_id`)

Counts people, folding a user's repeat searches into one. Reports 3 for 'laptop deals'. This is the reach metric.

Common follow-up questions

• How would you show only terms searched by at least 100 different people? (Wrap the query and filter with HAVING COUNT(DISTINCT `user_id`) >= N, testing that they filter on the aggregate rather than a raw column.)
• What if you needed unique searchers per term per day? (Add a date bucket from `query_time` to the grouping key for a per-day, per-term time series.)
• How would this hold up if the table grew to billions of rows and you needed it in near real time? (Tests awareness of approximate distinct counting such as HyperLogLog when exact COUNT(DISTINCT) gets expensive on billions of rows.)