# Above the Fold > Every click is a vote on the ranking. Score each search. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Meta ## Problem We score how well search did on each query: a 1 when the user clicked nothing, a 3 when they clicked one of the top three results, and a 2 when they clicked a result further down. Leave a query unrated when its click outcome was never captured, and return each query ID with its score. ## 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 200 rows: | query_id | rating | | --- | --- | | 2071 | 3 | | 2142 | 3 | | 2213 | NULL | | 2284 | 2 | | 2426 | NULL | ## Worked solution and explanation ### What this problem is really testing Strip the costume and this is a three-bucket labeling job: every query gets a 1, a 2, or a 3 based on where the user clicked. The real skill is expressing three mutually exclusive states as ordered CASE branches, and knowing that the branch you deliberately leave out matters as much as the ones you write. Anyone can map a click position to a score. The trap is the query whose click was never captured: it is not a no-click, and if you sweep it into rating 1 or into a catch-all ELSE, you invent a score for data you never had. > **The missing ELSE is the feature** > > Order the branches from most specific to least and write no ELSE. A recorded no-click (`clicked_result` = 0) becomes 1, a top-three click becomes 3, a lower click becomes 2, and a null `clicked_result` matches nothing, so CASE returns NULL on its own. The missing ELSE is the feature, not an oversight. --- ### Reading the click column `clicked_result` is the position the user clicked, counting from the top. A value of 0 means we recorded that they clicked nothing. A null means the outcome was never captured, which is a different thing entirely. `results_count` (how many results came back) is a decoy: the top-versus-lower split is about which position was clicked, never about how many results existed. #### Step 1: Score the recorded no-clicks Handle `clicked_result` = 0 first and map it to 1. Taking it up front keeps the later branches focused purely on real click positions. #### Step 2: Split clicks by position A click in positions 1 through 3 is a 3; a click at position 4 or beyond is a 2. The boundary is inclusive at 3, so BETWEEN 1 AND 3 captures the top tier and > 3 captures the rest. #### Step 3: Let unknown outcomes fall through Write no ELSE. A null `clicked_result` satisfies none of the branches, so CASE yields NULL, which is exactly the unrated result these queries deserve. Adding ELSE would overwrite that with a fabricated tier. --- ### The solution **Per-query CASE classification** ```sql SELECT query_id, CASE WHEN clicked_result = 0 THEN 1 WHEN clicked_result BETWEEN 1 AND 3 THEN 3 WHEN clicked_result > 3 THEN 2 END AS rating FROM search_queries ``` > **A null is not a no-click** > > Treating null as a no-click. Writing `clicked_result` IS NULL THEN 1 looks reasonable but scores queries you have no data for, and an ELSE 3 does the same damage from the other side. Check the expected output: null inputs come back unrated, not as 1. > **Two empty states, kept apart** > > Whether you notice there are two empty-looking states, not one, and keep them apart. Strong candidates also resist reaching for GROUP BY: the grain is already one row per query, so nothing needs collapsing. **Naive** CASE WHEN `clicked_result` IS NULL THEN 1 ... ELSE 3 END. Nulls get scored as 1 and every leftover state defaults to 3, so unknown outcomes are fabricated into tiers. **Correct** CASE WHEN `clicked_result` = 0 THEN 1 WHEN `clicked_result` BETWEEN 1 AND 3 THEN 3 WHEN `clicked_result` > 3 THEN 2 END. Nulls fall through to NULL and nothing is invented. > **One scan, nothing to sort or collapse** > > One sequential scan of `search_queries`, O(n) time and O(1) extra state. The CASE resolves per row in branch order, so most rows settle at the first or second branch, and the projection returns the same row count it read, with no sort or aggregation to pay for. --- ## Common follow-up questions - How would you average the score per `search_term` to surface the weakest queries? _(Tests rolling per-query labels into a per-term summary with conditional aggregation.)_ - If `clicked_result` could carry a position larger than `results_count` from a logging bug, how would you keep those rows out of the scored tiers? _(Tests guarding against impossible values instead of trusting the input.)_ - How would you make the top-three cutoff configurable so different surfaces can use different thresholds? _(Tests parameterizing a magic number instead of hardcoding it.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/above_the_fold) - [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.