# The Ones Who Clicked > Every signup cohort runs its searches. Find the years that turned queries into clicks. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 · Asked in: Microsoft ## Problem We're comparing search quality across signup cohorts for a shopping marketplace, grouping users by the calendar year they joined. Walk the cohorts from the earliest joiners to the most recent, and for each one return how many searches its users ran, how many of those ended in a clicked result, and the resulting success rate. ## 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 | ### users - user_id INTEGER - username TEXT - email TEXT - signup_date TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ## Expected output First 3 of 3 rows: | signup_cohort | total_searches | successful_searches | success_rate | | --- | --- | --- | --- | | 2024 | 50 | 0 | 0 | | 2025 | 50 | 48 | 0.96 | | 2026 | 50 | 46 | 0.92 | ## Worked solution and explanation ### What this really tests Strip the search-quality story and this is a per-cohort click-through rate. The skill being probed: can you compute successful over total for each signup year while getting the NULL semantics right. Everyone can group by a year and divide. What separates candidates is two silent traps: a NULL in `clicked_result` is not a zero-position click, it means no result was clicked, so it is an unsuccessful search that still belongs in the denominator; and integer division collapses every rate to 0 or 1 unless you cast. Miss the first and your successful counts drift; miss the second and every cohort reads 0.0. > **The NULL is the label** > > `clicked_result` is not a boolean. A non-null value is the position of the result the user clicked (a successful search); NULL means they clicked nothing. So successful = SUM(CASE WHEN `clicked_result` IS NOT NULL THEN 1 ELSE 0 END), and total = COUNT(*) over every joined search, nulls included. Never filter the nulls out in a WHERE, or you destroy the denominator. **Schema** - search_queries: query_id int PK, user_id int FK, clicked_result int, query_time timestamp - users: user_id int PK, signup_date date **Relationships** - search_queries.user_id references users.user_id *Each search points at a user; the user's signup year is the cohort.* --- ### Building it #### Step 1: Bucket each user by signup year A cohort is just the calendar year of `signup_date`. strftime('%Y', `signup_date`) turns a date into a four-character year label you can group on. Do it once in a CTE so the join downstream stays clean. #### Step 2: Attach searches to cohorts with an inner join Join `search_queries` to the cohort set on `user_id`. The inner join is deliberate: searches whose `user_id` is NULL or points at no user (the orphan rows in the sample) fall out, because a search with no owner belongs to no cohort. An outer join would smuggle them into a phantom bucket. #### Step 3: Aggregate in one pass, then cast before dividing COUNT(*) gives total searches per cohort. SUM(CASE WHEN `clicked_result` IS NOT NULL THEN 1 ELSE 0 END) counts the successful ones in the same pass. Cast that successful sum to REAL before dividing by the total, or SQLite does integer division and every rate rounds to 0 or 1. --- ### The solution **Per-cohort search success** ```sql WITH cohort AS ( SELECT u.user_id, strftime('%Y', u.signup_date) AS signup_cohort FROM users u ) SELECT c.signup_cohort, COUNT(*) AS total_searches, SUM(CASE WHEN sq.clicked_result IS NOT NULL THEN 1 ELSE 0 END) AS successful_searches, CAST(SUM(CASE WHEN sq.clicked_result IS NOT NULL THEN 1 ELSE 0 END) AS REAL) / COUNT(*) AS success_rate FROM search_queries sq JOIN cohort c ON sq.user_id = c.user_id GROUP BY c.signup_cohort ORDER BY c.signup_cohort ``` > **Integer division and the phantom equality** > > Two ways this goes sideways. Writing `clicked_result` = 1 treats the column as a flag when it is really the clicked position, so you only credit clicks that landed on the first result. And successful / total with no cast is integer division: 3/3 is 1, but 2/3 becomes 0. Cast one operand to REAL. > **Interviewers watch for** > > Whether you notice a NULL `clicked_result` must stay in the denominator, whether you reach for conditional aggregation instead of two separate COUNT queries, and whether you defend the inner join as the reason orphan searches disappear. Bonus points for casting the rate without being reminded. **Naive** WHERE `clicked_result` IS NOT NULL, then COUNT(*) as successful, plus a second query for totals, and successful/total as-is. The WHERE drops the failed searches from the denominator and integer division flattens the rate to 0 or 1. **Correct** One pass: COUNT(*) for total, SUM(CASE WHEN `clicked_result` IS NOT NULL...) for successful, CAST to REAL before dividing. Denominator intact, rate fractional. --- ## Common follow-up questions - How would you also break each cohort down by `age_bucket` without running the query a second time? _(Tests grouping by an additional dimension in the same pass.)_ - `search_queries` holds 80M rows partitioned by `query_time`. If you only wanted the last 30 days of searches, how would you keep the scan from touching every partition? _(Tests partition pruning against the `query_time` partition key.)_ - How would you flag the cohorts whose `success_rate` falls below the average `success_rate` across all cohorts? _(Tests comparing a per-group metric against an overall aggregate.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/search_success_by_user_tenure) - [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.