# Repeat Offenders of the Search Bar

> Once is a fluke. Twice is a habit.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The search team flags a 'repeat searcher' as any user who ran more than one search query during the year 2026. How many repeat searchers were there in 2026?

## Worked solution and explanation

### Why this problem exists in real interviews

The `search_queries` schema makes this a clean test of per-user aggregation combined with a count-based filter wrapped in a nested subquery. Columns like `search_term`, `results_count`, `clicked_result` introduce enough ambiguity that only candidates who clarify assumptions (what counts as a 'repeat searcher', how the year is derived) produce correct results.

---

### Break down the requirements

#### Step 1: Group by `user_id` within the year

`GROUP BY user_id` collapses each user's search rows into one group, after `WHERE strftime('%Y', query_time) = '2026'` restricts to searches run during the target year.

#### Step 2: Filter to more than one search and count

`HAVING COUNT(*) > 1` keeps only users with more than one search, then `COUNT(*)` over that result set counts the repeat searchers.

---

### The solution

**Count repeat searchers in 2026**

```sql
SELECT COUNT(*) AS repeat_searcher_count
FROM (
    SELECT user_id
    FROM search_queries
    WHERE strftime('%Y', query_time) = '2026'
    GROUP BY user_id
    HAVING COUNT(*) > 1
)
```

> **Cost Analysis**
>
> The inner aggregate groups every in-year row by `user_id`. An index on `(user_id, query_time)` lets the engine filter the year and group without a full sort of the table.

> **Interviewers Watch For**
>
> The interviewer checks whether you filter on COUNT(*) > 1 in a HAVING clause (per-group) rather than a WHERE clause, and whether you remember to count the qualifying users in an outer query instead of returning the rows themselves.

> **Common Pitfall**
>
> Putting the count condition in WHERE instead of HAVING fails because the filter must apply after aggregation. Also, COUNT(DISTINCT user_id) on the raw table counts all searchers, not just those with more than one search.

---

## Common follow-up questions

- How would your answer change if a 'repeat searcher' had to run more than one DISTINCT `search_term` rather than simply more than one query row? _(Tests awareness that 'more than one search' may need to mean more than one distinct term, not just more than one row.)_
- `strftime('%Y', query_time)` is applied to every row. Why can this prevent an index on `query_time` from being used, and how would a range predicate on `query_time` help? _(Tests knowledge of how SQLite derives the year and the cost of applying a function to a column in the WHERE clause.)_
- `query_id` in `search_queries` has ~60M distinct values. What index strategy keeps this aggregation from doing a full table scan? _(Tests whether the candidate can design indexes for high-cardinality columns and understands selectivity.)_
- Could you express this same repeat-searcher count without the nested subquery? What readability or correctness trade-off does that introduce? _(Tests whether the candidate can express the same count without a subquery and understands the readability trade-off.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/repeat_offenders_of_the_search_bar)
- [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.