# Age of Discovery

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

Canonical URL: <https://datadriven.io/problems/age_of_discovery>

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.

## 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.

> **Cast before you divide**
>
> successful_searches and total_searches are both integers. In SQLite (and most engines) an integer divided by an integer truncates, so 3/5 is 0, not 0.6. Wrap the numerator in CAST(... AS REAL) before the division and every rate stays a real decimal. Miss it and all six cohorts report a success rate of 0.0 while the counts next to them look perfect.

### 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.

**Success rate per age cohort**

```sql
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;
```

*One pass, one join: scope the rows, count conditionally, cast then divide.*

> **Integer division is the silent killer**
>
> The most common wrong answer here runs clean, returns the right cohorts and the right counts, and reports every success_rate as 0.0. That is integer division truncating. The fix is one CAST, and it is the first thing an interviewer checks the output for.

> **What the interviewer is watching**
>
> They want to see where you put the null handling. A candidate who writes LEFT JOIN and then filters, or who counts straight from search_queries without excluding the blank cohort, inflates the denominator with searches that belong to no cohort. Scoping the rows before you aggregate is the tell of someone who has debugged a wrong rate in production.

**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.

> **In production**
>
> Success-rate dashboards break this exact way. A rate that reads 0% across the board is almost never a product collapse; it is an integer-division bug in the query. Storing the numerator and denominator alongside the rate, as this query does, lets on-call spot it in a single glance.

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/age_of_discovery)
- [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.