Above the Fold

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

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.

Every click is a vote on the ranking. Score each search.

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

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.

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

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

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.

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