# Search Algorithm Rating

> How good are the search results?

Canonical URL: <https://datadriven.io/problems/search_algorithm_rating>

Domain: SQL · Difficulty: hard · Seniority: L3

## Problem

We need a search quality score for each query. Assign a rating: 1 if no result was clicked, 2 if a result was clicked but the top clicked position was outside the top 3, and 3 if a result was clicked in positions 1 through 3. Show each query ID and its rating.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a `CASE WHEN` classification problem dressed up in product language. The interviewer wants to see that you can translate three product rules into mutually exclusive WHEN branches, get the order right, and resist the urge to add aggregation that the question never asked for.

> **Trick to Solving**
>
> The output is **one row per query**, not per term. Treat each row in `search_queries` independently and label it 1, 2, or 3 with a single `CASE` expression. Two columns out: `query_id` and `rating`.

---

### Break down the requirements

#### Step 1: Rate 1: no result was clicked

`clicked_result` is 0 or 1. The first branch is the no-click case: `WHEN clicked_result = 0 THEN 1`. Cover this first so later branches only deal with clicks.

#### Step 2: Rates 2 and 3: click position vs top 3

When a result was clicked, decide on rate 2 vs 3 by whether the click landed in the top 3 positions. The schema exposes `results_count` (how many results were shown). Map `results_count > 3` to rate 2 (click landed outside the top 3) and `results_count <= 3` to rate 3 (click was in the top 3).

#### Step 3: Project query_id and rating

Project exactly two columns: `query_id` and the labeled `rating`. No GROUP BY, no aggregation, no LIMIT. The grader compares row-for-row against `search_queries`.

---

### The solution

**Per-query CASE classification**

```sql
SELECT
    query_id,
    CASE
        WHEN clicked_result = 0 THEN 1
        WHEN clicked_result = 1 AND results_count > 3 THEN 2
        WHEN clicked_result = 1 AND results_count <= 3 THEN 3
    END AS rating
FROM search_queries
```

> **Time and Space Complexity**
>
> **Time:** O(n) single scan over `search_queries`. The CASE evaluates branches in order, so most rows resolve at branch 1 or 2.
> 
> **Space:** O(1) extra; the projection has the same row count as the input.

> **Interviewers Watch For**
>
> Strong candidates check `clicked_result = 0` first so the click branches don't need to repeat that condition. They also notice that the prompt says nothing about aggregation or ranking and resist building a leaderboard.

> **Common Pitfall**
>
> Adding `GROUP BY query_id` or aggregating across `search_term`. The output grain is one row per query exactly as it appears in the source table; aggregation collapses rows and changes the shape.

---

## Common follow-up questions

- How would you average the rating per `search_term` to surface the worst-performing terms? _(Tests rolling per-query labels up to a per-term quality summary using conditional aggregation.)_
- What if `clicked_result` could be NULL, meaning the click event didn't fire? Which branch would catch it and how would you fix it? _(Tests handling unexpected values without breaking the rating distribution.)_
- How would you make the position threshold (3) configurable across surfaces so the home page and the search page can use different cutoffs? _(Tests using a lookup table or computed column instead of inline CASE.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/search_algorithm_rating)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.