Diminishing Returns

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Meta

Problem

Some searches in search_queries never logged how many results came back, so they can't be sized and get their own unknown group. Every other search is small below 100 results, medium from 100 to 299 and large from 300 up, and we want each group's click-through rate as a percentage rounded to two decimals, highest rate first.

When search comes back thin, does the click still follow?

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 is really testing

Strip the search-analytics costume and this is a bucket-then-rate with a hole in the bucketing column. Everyone writes the size bands. What separates candidates is where the searches with no results_count land. A NULL fails results_count < 100 and results_count < 300 alike, so a CASE ending in ELSE 'large' quietly files those 14 unsized searches as large. None of them has a click, so the large rate drops from 65.63 to 53.85, and the trend you report is partly a logging gap. The second trap is the click itself: clicked_result holds the clicked position, or NULL when nothing was clicked, so the signal is presence, not value.

Build it in two moves

Step 1: Label each search by size, NULL first

Turn results_count into a label with a CASE: IS NULL becomes 'unknown', below 100 'small', below 300 'medium', and everything else 'large'. The NULL test has to lead. Every comparison against NULL is unknown, so an unsized search slides past each band and lands wherever the ELSE points. Ascending bands with strict < let each branch rely on the earlier ones having failed, so 299 is medium and 300 is large without restating lower bounds.

Step 2: Rate the clicks inside each label

Inside each label, a click is any row where clicked_result IS NOT NULL. Count those with SUM(CASE WHEN clicked_result IS NOT NULL THEN 1 ELSE 0 END) and divide by COUNT(*) for the label. The 100.0 scales to a percentage and also forces decimal division; without it, the integer ratio truncates every rate to 0. ROUND(..., 2) gives the two decimals.

The solution

SELECT
    CASE
        WHEN results_count IS NULL THEN 'unknown'
        WHEN results_count < 100 THEN 'small'
        WHEN results_count < 300 THEN 'medium'
        ELSE 'large'
    END AS result_bucket,
    ROUND(100.0 * SUM(CASE WHEN clicked_result IS NOT NULL THEN 1 ELSE 0 END) / COUNT(*), 2) AS click_through_pct
FROM search_queries
GROUP BY result_bucket
ORDER BY click_through_pct DESC

Wrong: equals 1

clicked_result = 1 counts only clicks on the first result. Every click on position 2 or lower reads as a non-click, so every rate is understated.

Right: is not null

clicked_result IS NOT NULL counts every search that ended in a click, whatever the position, and treats only the NULL rows as non-clicks.

Common follow-up questions

• How would you turn this into a daily trend of click-through rate per size group? (Add `DATE(query_time)` to the `GROUP BY` alongside the label.)
• Leadership wants one overall click-through number. Why not average the four percentages? (The groups hold different row counts; the overall rate is total clicks over total searches.)
• If some searches logged zero results, where should they go? (A zero-result search cannot be clicked; a separate band keeps it from dragging small down, tested before the small band.)