# 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 ## Problem We're comparing search quality across signup cohorts for a shopping marketplace, grouping users by the calendar year they joined. For each cohort, return how many searches its users ran, how many of those ended in a clicked result, and the resulting success rate. ## 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. --- ### 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/the_ones_who_clicked) - [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.