The Ones Who Clicked

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

Microsoft

Problem

A search only counts as a success when the user clicked one of its results, and on our shopping marketplace everyone in users who signed up in the same calendar year forms one cohort. List the cohorts from earliest to most recent with how many search_queries their users ran, how many of those succeeded, and the success rate.

Every signup cohort runs its searches. Find the years that turned queries into clicks.

Tables

search_queries

Columns: query_id INTEGER, user_id INTEGER, search_term TEXT, results_count INTEGER, clicked_result INTEGER, query_time TEXT

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

Expected output

Worked solution and explanation

What this really tests

Strip the search-quality story and this is a per-cohort click-through rate over a join. Anyone can join searches to users and divide. What separates candidates is three quiet decisions. First, the cohort is the year sliced out of a text signup_date, and some rows carry a time part, so keying on the raw value gives you a bucket per signup day. Second, a NULL clicked_result is a search nobody clicked, and it stays in the denominator. Third, successful over total is integer division unless you cast. Miss the first and you return dozens of rows; miss the last and 2025's 0.96 reads 0.

Building it

Step 1: Bucket each user by signup year

A cohort is the calendar year of signup_date, stored as text, sometimes with a time part ('2025-08-08 16:34:00'). strftime('%Y', u.signup_date) reads the year off either form and returns a four-character label. Do it once in the cohort CTE so the join downstream stays clean.

Step 2: Attach searches to cohorts with an inner join

Join search_queries to cohort on user_id. The inner join is deliberate: searches whose user_id is NULL, the orphan rows in the sample, fall out, because a search with no owner belongs to no cohort. An outer join would carry them into a phantom NULL bucket.

Step 3: Aggregate in one pass, then cast before dividing

COUNT(*) gives total searches per cohort. SUM(CASE WHEN sq.clicked_result IS NOT NULL THEN 1 ELSE 0 END) counts the successful ones in the same scan. Cast that sum to REAL before dividing by the total, or the engine does integer division and 48 over 50 comes back as 0.

The solution

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

Naive

WHERE clicked_result IS NOT NULL, then COUNT(*) as successful, a second query for totals, and successful / total as-is. The filter drops failed searches from the denominator and integer division flattens the rate.

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 adding a dimension to the same pass, and what a NULL `age_bucket` does to the grouping.)
• `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 rate across all attributed searches combined? (Tests comparing a per-group metric against an overall aggregate, and whether a mean of rates is the right baseline.)