# The Relentless Searchers > Most users look once and leave. A few never stop looking. Canonical URL: 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. ## 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 5 of 76 rows: | user_id | query_count | rnk | | --- | --- | --- | | NULL | 50 | 1 | | 100 | 2 | 2 | | 1070 | 2 | 3 | | 1264 | 2 | 4 | | 1361 | 2 | 5 | ## 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 **Count once, number over a total order** ```sql 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. > **The key matters more than the function** > > Once the window orders by count and then text `user_id`, no two rows tie, so `RANK()`, `DENSE_RANK()` and `ROW_NUMBER()` all return identical numbers. The skill is making the order total; the numbering function only reads it off. > **Text order is not number order** > > Order the tie-break by `user_id` as a number and 9 sorts before 10; as text, '10' comes first. The two orderings disagree on exactly the tied users the tie-break exists for. > **A window can read the group's aggregate** > > Many candidates wrap the count in a staging query before numbering. Because `OVER` is evaluated after grouping, `ORDER BY COUNT(*)` inside the window already sees each user's total, so one `SELECT` does the whole job. > **Sort after the data shrinks** > > The scan touches 60,000,000 rows but only the `user_id` column. Aggregation collapses them to roughly 3,000,000 groups, and only those get sorted for the window. Number before counting and you sort sixty million rows for nothing. > **Say the tie out loud first** > > The senior move is to name it before typing: 'counts will collide, so I need a second key that makes the order total.' Candidates who discover the ties only after the output looks wrong read as mid-level. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_relentless_searchers) - [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.