Age of Discovery

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

L5

Problem

The search team wants to see how well different age cohorts find what they're after on our shopping platform. For every cohort with an age band on file, show the total searches, how many of those clicked through to a result, and the success rate as a decimal, listed alphabetically by cohort.

Every generation searches differently. See who finds what they came for.

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 you're actually being asked

Strip the shopping-platform costume and this is a per-group ratio: for each cohort, successful searches over total searches. Anyone can write the grouping. Two quieter decisions separate the candidates who get hired. First, which searches are even allowed into the denominator. Second, keeping that ratio in floating point so it doesn't collapse to zero.

The build, step by step

Step 1: Decide which rows count

Two kinds of noise sit in search_queries. Rows with a null user_id can't be tied to anyone, and users with no age band on file can't be placed in a cohort. An inner join to users drops the orphan searches for free, and a WHERE u.age_bucket IS NOT NULL removes the recorded-but-blank cohort. Both belong in the scoping, not the SELECT list.

Step 2: Count success conditionally

A search is successful when the user clicked through, which the data records as a non-null clicked_result (the position they clicked, so 1, 2, 8, whatever). SUM(CASE WHEN sq.clicked_result IS NOT NULL THEN 1 ELSE 0 END) counts exactly those, alongside COUNT(*) for the denominator. Reading clicked_result = 1 instead would only catch people who clicked the top result and quietly undercount every cohort.

Step 3: Divide in floating point

Take the conditional sum, cast it to REAL, and divide by COUNT(*). Grouping by age_bucket collapses everything to one row per cohort, and ordering by the label lists them 18-24 first through 65+ last.

SELECT
    u.age_bucket,
    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
INNER JOIN users u ON sq.user_id = u.user_id
WHERE u.age_bucket IS NOT NULL
GROUP BY u.age_bucket
ORDER BY u.age_bucket;

Naive

LEFT JOIN users, no age_bucket filter, and successful_searches / total_searches. Orphan and blank-cohort searches leak into the counts, and integer division flattens every rate to 0.

Correct

INNER JOIN plus WHERE age_bucket IS NOT NULL scopes the rows, and CAST(... AS REAL) keeps the ratio a decimal. Counts and rates both hold up.

Common follow-up questions

• How would you also report each cohort's share of total successful searches across the whole platform? (Tests a window function or a scalar subquery over the grouped result.)
• The team wants cohorts with fewer than 20 searches merged into an 'other' bucket. How? (Tests CASE-based regrouping and threshold logic on aggregates.)
• How would you extend this to a success rate per cohort per month? (Tests adding a time dimension to the grouping key.)