Repeat Offenders of the Search Bar

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

Capital One

Problem

Each row of search_queries is one search, stamped with its query_time and the user_id that ran it, and logged-out searches all land under a blank user_id that is treated as a single searcher. How many searchers ran more than one search in 2026?

Once is a fluke. Twice is a habit.

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

This is a count of groups that cleared a bar, dressed up as a search metric. Everyone writes GROUP BY user_id HAVING COUNT(*) > 1; what separates candidates is realizing that clause hands back a list of users, not a number, so a second aggregate has to count the survivors. The shortcut, COUNT(DISTINCT user_id) over the year, returns 75 instead of 16: it counts everyone who searched at all and the 'more than one' threshold silently does nothing.

Two aggregates stacked

Step 1: Restrict to 2026 before collapsing

The year filter has to run on raw rows, before any grouping, so a user's searches from earlier years never pad their total. Drop it and every one of the 75 seeded users has two searches across the whole table, so all of them qualify.

Step 2: Collapse to one row per user_id and keep the repeaters

GROUP BY user_id turns each searcher into one group, and HAVING COUNT(*) > 1 throws away the groups with a single search. This is a group-level test, which is exactly why it cannot live in WHERE.

Step 3: Count the surviving groups

The inner query returns one row per repeat searcher. Wrapping it and taking COUNT(*) turns that list into the single number the question asks for.

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
)

One pass

COUNT(DISTINCT user_id) over the 2026 rows returns 75. It counts every searcher, skips the blank-ID bucket, and never applies the more-than-one test.

Group, filter, count

GROUP BY user_id HAVING COUNT(*) > 1, then COUNT(*) over the result, returns 16: the 15 named users with two searches plus the logged-out bucket.

Common follow-up questions

• How would the answer change if a repeat searcher needed more than one different `search_term`, not just more than one row? (Tests whether the candidate swaps the row count for a count of unique terms, and notices the padded and mixed-case terms in the data.)
• Product now says logged-out searches should not count as a searcher. What changes, and what is the new number? (Tests whether the candidate sees that the blank `user_id` group is the one that drops out, leaving 15.)
• Could you express this without the nested query? What do you give up? (Tests knowledge of an aggregate over a window or a CTE, and the readability trade-off.)
• `search_queries` grows to billions of rows a year. How would you keep this count cheap to refresh daily? (Tests incremental per-user counts kept in a summary table instead of a full rescan.)