# First Among Results > When queries run long, does the top hit still win? Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 ## Problem The search relevance team wants to know whether longer queries push shoppers onto the very first hit. In `search_queries`, look only at searches that ended in a click: `clicked_result` holds the position of the result clicked, where 1 is the top result and an empty value means no click. For each search term length, show how many of these clicked searches there were and how many landed on that top result, from shortest term to longest. ## Worked solution and explanation ### What this problem is really testing On the surface this is a click-through report by search term length. Underneath, `clicked_result` is not a yes/no flag: it is the position the shopper clicked, and an empty value means they clicked nothing at all. Two edges bite. First, the report is about searches that ended in a click, so the no-click rows have to leave before you count, or your per-length totals quietly include searches nobody acted on. Second, only position 1 is the top result: reach for `COUNT(clicked_result)` and a click on the ninth result counts exactly like a click on the first, inflating the top-result number on every length. > **Trick to solving** > > When only one specific value of a column should count, do not reach for `COUNT(column)`: that counts every non-null value. Push the condition inside the aggregate with `SUM(CASE WHEN clicked_result = 1 THEN 1 ELSE 0 END)` so every other position folds to zero, all in a single pass. And scope the whole report to real clicks with `WHERE clicked_result IS NOT NULL` so the total means clicked searches, not all searches. **COUNT(clicked_result)** Counts every non-null value, so clicks on position 2, 5, or 9 all count. This answers 'how many clicked anything', not 'how many clicked the top result', and it inflates the intended number on this data. **SUM(CASE WHEN clicked_result = 1 THEN 1 ELSE 0 END)** Counts only the rows where the top result was clicked. Every other position contributes 0. This is the metric the prompt actually asks for. --- ### Walking through it #### Step 1: Keep only clicked searches Keep only searches that ended in a click with `WHERE clicked_result IS NOT NULL`. The no-click rows have an empty position and would pad the per-length total with searches nobody acted on, changing what `query_count` means. #### Step 2: Derive the length dimension Group by `LENGTH(search_term)`. The length itself is the reporting bucket, so compute it once and reuse the alias in the GROUP BY and ORDER BY. #### Step 3: Count the clicked searches `COUNT(*)` gives the total clicked searches at each length. Because the no-click rows are already gone, this denominator means 'searches that ended in a click', which is what the team compares against. #### Step 4: Conditionally count top-result clicks `SUM(CASE WHEN clicked_result = 1 THEN 1 ELSE 0 END)` counts only clicks on the top result. This is the line that separates candidates from the `COUNT(clicked_result)` crowd who never checked what the column actually holds. #### Step 5: Sort the output `ORDER BY term_length` returns shortest to longest, matching the preview so the trend by length reads top to bottom. --- ### The solution **Top-result clicks among clicked searches, by term length** ```sql SELECT LENGTH(search_term) AS term_length, COUNT(*) AS query_count, SUM(CASE WHEN clicked_result = 1 THEN 1 ELSE 0 END) AS top_click_count FROM search_queries WHERE clicked_result IS NOT NULL GROUP BY term_length ORDER BY term_length ``` > **Interviewers watch for** > > They watch whether you scope to real clicks and whether you read `clicked_result` as a flag or as a position. Leaving the no-click rows in, or writing COUNT(clicked_result), are the fast-and-wrong answers; filtering on IS NOT NULL and scoping the SUM to `= 1` shows you inspected the column's values before writing SQL. > **Common pitfall** > > `COUNT(clicked_result)` in place of the conditional SUM, or forgetting the `IS NOT NULL` filter entirely. Both compile and return plausible-looking numbers, and both are wrong: one counts clicks below the top result, the other pads every length with searches nobody clicked. Always inspect a column's values before treating it as boolean. > **Cost analysis** > > At ~80M rows the single GROUP BY over `LENGTH(search_term)` is one sequential scan, and the filter plus the CASE add no extra pass. `LENGTH(search_term)` is not sargable, so an index on `search_term` will not help this aggregate: the cost is the scan itself, and there is no cheaper plan for a full-table rollup. --- ## Common follow-up questions - How would the query change if the team wanted clicks on any of the top three results instead of only the very first? _(Tests whether the candidate can generalize the CASE condition from one value to a range.)_ - If you left the no-click searches in the total instead of filtering them out, which number would change and why? _(Tests understanding of how the null filter changes the denominator.)_ - If you also needed the top-result click-through rate per length, how would you compute it without a second query? _(Tests computing a ratio via conditional aggregation in the same pass.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/first_among_results) - [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.