First Among Results

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

ESPN

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.

When queries run long, does the top hit still win?

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

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

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

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