# Everybody Wants a Bigger Screen > The search bar never lies about what people actually want. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The peripherals merchandising team is sizing display demand before the next buy and wants to know who keeps coming back to search for monitors. For each user in the search_queries log, count the searches whose term mentions a monitor, matching any capitalization so 'Monitor' and 'MONITOR' still count, and list the heaviest monitor searchers first. ## 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 'Monitor arm' and 'MONITOR stand' have to count exactly like '4k monitor'. Miss the case fold and you silently undercount your heaviest shoppers, which are the exact people the merchandising team is trying to find. --- ### Break it down #### Step 1: Filter to monitor intent, case and all Normalize the text before you match it: `LOWER(search_term) LIKE '%monitor%'`. The leading and trailing wildcards let 'monitor' sit anywhere in the term (start, middle, end), and the `LOWER()` fold is what pulls in 'Monitor', 'MONITOR', and 'ultrawide Monitor' alongside the already-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. Users whose searches never mention a monitor fall out entirely because the WHERE clause runs before the grouping, so they never reach a group. #### Step 3: Heaviest searchers first Sort so the most active monitor shoppers surface first: `ORDER BY monitor_searches DESC`, then `user_id` to make the order deterministic when two users tie on the same count. --- ### The solution **Count monitor-intent searches per user** ```sql SELECT user_id, COUNT(*) AS monitor_searches FROM search_queries WHERE LOWER(search_term) LIKE '%monitor%' GROUP BY user_id ORDER BY monitor_searches DESC, user_id ``` **Case-blind filter (wrong)** `WHERE search_term LIKE '%monitor%'` only catches the lowercase spellings. User 200's 'MONITOR stand' and 'curved monitor' collapse to a count of 1, and user 100's 'Monitor arm' vanishes. The heavy shoppers look average. **Case-folded filter (correct)** `WHERE LOWER(search_term) LIKE '%monitor%'` folds every spelling to the same case first, so user 100 and user 200 each correctly show 2. The people worth chasing rise to the top. > **Common pitfall** > > The classic miss is writing `search_term LIKE '%monitor%'` and moving on. It runs, it returns rows, and it looks right on a demo, but it quietly drops every capitalized variant and undercounts the exact users the report exists to surface. Fold the column with `LOWER()` (or lean on a case-insensitive collation) before the match, every time. > **Interviewers watch for** > > Interviewers watch whether you reach for case-insensitivity without being told, and whether you know why the filter belongs in WHERE and not HAVING here (there is no aggregate in the predicate, so it filters rows before grouping). Naming a deterministic tie-break in the ORDER BY is the small tell that you have shipped ranked reports before. > **At scale** > > At 50,000,000 rows, wrapping `search_term` in `LOWER()` plus a leading-wildcard `LIKE` defeats a plain B-tree index, forcing a full scan. In production you would back this with a functional index on `LOWER(search_term)` or a trigram / inverted index, and if the monitor 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.)_ - Merchandising now wants monitors, keyboards, and mice sized side by side. How would you turn this into one row per category? _(Tests whether the candidate can extend a substring filter to several intent keywords without losing the case fold.)_ - Your `LOWER(search_term) LIKE '%monitor%'` 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.