The Relentless Searchers

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

Google

Problem

Every row in search_queries is one search, and activity is lopsided enough that many users end up on the same total. Return each user with their query total and a whole-number position starting at 1 for the most active, listed in position order. No two users share a position: equal totals are settled by user ID read as text, alphabetically.

Most users look once and leave. A few never stop looking.

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

This is a strict total ordering dressed up as a search leaderboard. Anyone can write GROUP BY user_id with COUNT(*). What separates candidates is seeing that the count is not an ordering on its own: search_queries is long-tailed, so whole crowds of users share a total. Number on the count alone and those users either share a slot or get shuffled into whatever order the engine felt like. Settle the tie on user_id as a number instead of as text and they land in the wrong slots. The second key is the whole problem, and the grader checks every position.

Build it in one pass

Step 1: Count searches per user

GROUP BY user_id with COUNT(*) gives each user's total. Rows with a NULL user_id collapse into one group of their own, and that group stays: no WHERE user_id IS NOT NULL, no inner join to a users table.

Step 2: Order the window on the aggregate itself

Window functions run after GROUP BY, so the window can sort on COUNT(*) directly in the same SELECT: no staging step needed. It orders by COUNT(*) DESC first, then CAST(user_id AS TEXT) ASC. The count settles almost every position; the text key only speaks inside a tie, and it has to be text because the statement asks for alphabetical order of the ID.

Step 3: Number by position, then list in that order

ROW_NUMBER() hands out 1, 2, 3 down that order. The final ORDER BY rnk lists the rows in position order, and since rnk is unique there are no ties left to argue about.

The solution

SELECT
    user_id,
    COUNT(*) AS query_count,
    ROW_NUMBER() OVER (
        ORDER BY COUNT(*) DESC, CAST(user_id AS TEXT) ASC
    ) AS rnk
FROM search_queries
GROUP BY user_id
ORDER BY rnk

Ordering on the count alone

RANK() OVER (ORDER BY COUNT(*) DESC) gives the four users tied at 2 the same rnk and then skips three numbers. ROW_NUMBER() over the same one-key order gives them different numbers, but which user gets which is up to the engine.

Count, then text ID

Adding CAST(user_id AS TEXT) as the second key gives every user their own number, none skipped, and the same assignment on every run.

Common follow-up questions

• What changes if `query_id` can repeat because ingest replays events? (Tests switching `COUNT(*)` to `COUNT(DISTINCT query_id)` and what that costs.)
• The leaderboard should cover only the last month. How do you avoid reading the whole year? (Tests partition pruning: filter on `query_time`, the partition key, before grouping.)
• Number users within each day instead of globally. What changes in the window? (Tests adding `PARTITION BY` on the day and grouping by user and day.)