# Everybody Wants a Bigger Screen

> The search bar never lies about what people actually want.

Canonical URL: <https://datadriven.io/problems/everybody_wants_a_bigger_screen>

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.

## 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 5 rows:

| user_id | keyboard_searches |
| --- | --- |
| 488 | 2 |
| 2428 | 2 |
| 4368 | 2 |
| 6308 | 2 |
| 8248 | 2 |

## 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

**Count keyboard-intent searches per user**

```sql
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.

> **Plausible output hides the dropped spellings**
>
> The classic miss is writing `search_term LIKE '%mechanical keyboard%'` and moving on. It runs, it returns rows, and it looks right on a demo, but it quietly drops every capitalized variant. Fold the column with `LOWER()` (or lean on a case-insensitive collation) before the match, every time.

> **Folding case before anyone asks**
>
> Interviewers watch whether you reach for case-insensitivity without being told, and whether you know the filter belongs in `WHERE` and not `HAVING`: there is no aggregate in the predicate, so it removes rows before grouping.

> **A leading wildcard means a full scan**
>
> At 50,000,000 rows, wrapping `search_term` in `LOWER()` plus a leading-wildcard `LIKE` defeats a plain B-tree index and forces a full scan. In production you would back this with a trigram or inverted index on `LOWER(search_term)`, and if the cut is queried constantly, a pre-aggregated per-user rollup pays for itself.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/everybody_wants_a_bigger_screen)
- [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.