Average Search Results Per User

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

Some shoppers say search keeps coming back thin while others get pages of hits, so the personalization team wants it measured per person from the search_queries log: each signed-in user's average number of results returned, from the lowest user id to the highest. A user whose searches never logged a result count still belongs on the list, with an empty average.

How many results per searcher?

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 per-user average with a ghost in the data. Anyone can write AVG(results_count) per user_id. What separates candidates is noticing that 50 of the 200 rows in search_queries have no user_id at all. Group straight away and those anonymous searches fold into one extra row with an empty user, averaging strangers together as if they were one shopper. Skip the per-user split entirely and you get one number for the whole log, which says nothing about which users see richer results.

Step 1: Pick the grain: one row per user

The question is about people, so the output grain is user_id. Everything lives on search_queries, so no join is needed. Grouping by query_id would hand back the 200 raw searches unchanged.

Step 2: Set aside anonymous searches first

WHERE user_id IS NOT NULL runs before grouping, so the anonymous rows never form a bucket. A NULL key is not a user, but GROUP BY still collects every NULL into a single group, and that group is exactly the phantom row.

Step 3: Average results_count inside each group

AVG skips NULL inputs, so a search that logged no count does not drag the mean toward zero. Four users have only uncounted searches. Their group survives with an empty avg_results, which is the honest answer: nobody knows how rich their results were.

Step 4: List by user_id ascending

The team reads the list from the lowest user id up. user_id is unique per output row, so ORDER BY user_id fully determines the sequence and needs no tiebreaker.

The solution

SELECT user_id, AVG(results_count) AS avg_results
FROM search_queries
WHERE user_id IS NOT NULL
GROUP BY user_id
ORDER BY user_id

Grouped straight away

GROUP BY user_id on the raw table returns 76 rows: the 75 real users plus one row whose user_id is NULL, averaging every anonymous search as if one person ran them all.

Filtered, then grouped

WHERE user_id IS NOT NULL first, then GROUP BY user_id: 75 rows, each summarizing one real user's own searches.

Common follow-up questions

• How would you list only users whose average beats the site-wide average of `results_count`? (Tests comparing a per-group aggregate to a global one.)
• A user with two lucky searches can top a richest-results list. How would you make the comparison fairer? (Tests awareness that small samples make averages noisy: a minimum search count, or a median.)
• How would you add the share of each user's searches that returned zero results? (Tests conditional aggregation at the same per-user grain.)
• What changes if the team wants anonymous searches shown as a single 'anonymous' row at the end of the list? (Tests labeling a `NULL` key deliberately and controlling where it sorts.)