# Who's Looking > Every search is a question someone needed answered. Count the people asking. Canonical URL: 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. ## Tables ### search_queries - query_id INTEGER - user_id INTEGER - search_term TEXT - results_count INTEGER - clicked_result INTEGER - query_time TEXT Sample rows (10 of 200): | query_id | user_id | search_term | results_count | clicked_result | query_time | | --- | --- | --- | --- | --- | --- | | 2071 | 100 | laptop deals | 17 | 2 | 2026-02-02 01:11:00 | | 2142 | 197 | phone case iphone 15 | 34 | 3 | 2026-03-03 02:22:00 | | 2213 | 294 | usb-c cable 6ft | 51 | NULL | 2026-04-04 03:33:00 | | 2284 | NULL | gaming mouse | 68 | 5 | 2026-05-05 04:44:00 | | 2355 | 488 | mechanical keyboard | 85 | 6 | 2026-06-06 05:55:00 | | 2426 | 585 | 4k monitor | 102 | NULL | 2026-07-07 06:06:00 | | 2497 | 682 | standing desk | 119 | 8 | 2026-08-08 07:17:00 | | 2568 | NULL | webcam 1080p | 136 | 9 | 2026-09-09 08:28:00 | | 2639 | 876 | bluetooth speaker | 153 | NULL | 2026-01-10 09:39:00 | | 2710 | 973 | smart watch | 170 | 1 | 2026-02-11 10:50:00 | ## Expected output First 5 of 33 rows: | search_term | unique_searchers | | --- | --- | | standing desk | 5 | | smart watch | 5 | | usb-c cable 6ft | 4 | | usb-c cble 6ft | 1 | | wireless headphones | 0 | ## 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 **Unique searchers per term** ```sql 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. > **Every version runs, only one counts people** > > `COUNT(*)`, `COUNT(user_id)` and `COUNT(DISTINCT user_id)` all execute without error and all return one number per term. That is why the wrong one survives code review: the first two answer how many searches, never how many different people. > **Anonymous rows need no filter** > > Noticing the `NULL` `user_id` rows and knowing `COUNT(DISTINCT ...)` already ignores them is the tell. A `WHERE user_id IS NOT NULL` would also delete the all-anonymous terms that should report 0. > **One aggregation pass, no self-join** > > At 25M rows and 2.5M terms the engine hashes rows into per-term groups and keeps a set of user ids per group. User ids are small integers, so this stays a single aggregation; only the head terms of a skewed distribution grow large sets. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/who_s_looking) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.