# Diminishing Returns > When search comes back thin, does the click still follow? Canonical URL: 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. ## 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 4 of 4 rows: | result_bucket | click_through_pct | | --- | --- | | small | 69.57 | | medium | 65.79 | | large | 65.63 | | unknown | 0 | ## 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 **Label with a `NULL` guard, then a conditional rate per label** ```sql 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 ``` > **`ELSE 'large'` is not 300 or more** > > The bands look like a complete split of the numbers, so candidates write three branches and stop. On this data, that puts 14 unsized and unclicked searches in large. Its rate falls from 65.63 to 53.85, the unknown row vanishes, and the medium-to-large drop jumps from a fraction of a point to almost twelve. > **Presence is the click, not the position** > > `clicked_result` stores which result was clicked, 1 through 10, and is `NULL` when the shopper clicked nothing. Writing `clicked_result = 1` or `SUM(clicked_result)` means you assumed a flag without reading the sample rows. Testing `IS NOT NULL` shows you read the column before using it. **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. > **One scan, four keys** > > One pass over `search_queries`, a per-row `CASE`, and a hash aggregate with four keys. At 50M rows, the state is four accumulators: no join, no subquery, no second pass. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/diminishing_returns) - [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.