Everybody Wants a Bigger Screen

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

Count how many times each user searched for a mechanical keyboard in our search_queries log, including terms where the phrase sits inside a longer query. Terms are stored as typed, so 'Mechanical Keyboard' and 'mechanical keyboard' are the same search.

The search bar never lies about what people actually want.

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 tally of a fuzzy text filter wearing a demand-sizing costume. The skill being probed: can you count rows per user while making a substring match survive capitalization? Anyone can type GROUP BY user_id. The catch is that 'Mechanical Keyboard' has to count exactly like 'mechanical keyboard'. Miss the case fold and you silently undercount real shoppers, the exact people the team is trying to size.

Break it down

Step 1: Filter to keyboard intent, case and all

Normalize the text before you match it: LOWER(search_term) LIKE '%mechanical keyboard%'. The leading and trailing wildcards let the phrase sit anywhere in the term, including the rows logged with stray spaces around it, and the LOWER() fold is what pulls in 'Mechanical Keyboard' alongside the lowercase hits.

Step 2: Count per user

Collapse the surviving rows to one row per shopper with GROUP BY user_id, and COUNT(*) the searches inside each group. COUNT(*) rather than a count of unique terms, because a repeat search is a return visit and is the signal. Users who never searched the phrase fall out because WHERE runs before the grouping, and the logged-out searches land together in one NULL group.

The solution

SELECT user_id, COUNT(*) AS keyboard_searches
FROM search_queries
WHERE LOWER(search_term) LIKE '%mechanical keyboard%'
GROUP BY user_id

Case-blind filter (wrong)

WHERE search_term LIKE '%mechanical keyboard%' only catches the lowercase spellings. A user who typed 'Mechanical Keyboard' vanishes, and one who mixed spellings shows half their real count.

Case-folded filter (correct)

WHERE LOWER(search_term) LIKE '%mechanical keyboard%' folds every spelling to the same case first, so both users come out with their real counts.

Common follow-up questions

• Some searches have a NULL `user_id`. Should they appear as their own group, and how would you keep them out of a per-shopper report? (Tests handling of unattributed rows once the grain is per user.)
• The team now wants keyboards, mice and headsets sized side by side. How would you turn this into one row per user and category? (Tests whether the candidate can extend a substring filter to several intent phrases without losing the case fold.)
• Your `LOWER(search_term) LIKE '%mechanical keyboard%'` cannot use a standard index on `search_queries`. What index would make this filter cheap? (Tests indexing knowledge for a leading-wildcard, case-folded predicate at scale.)