# Gone to Ground

> The control group held steady. Some of them slipped away. Find the ones who stopped answering.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

Our mobile A/B tests keep a control group that runs on Android, and we're checking which of those users have gone quiet. For each one whose account is no longer active, return their age bracket alongside the platform and variant, oldest experiment assignment first.

## Worked solution and explanation

### What this is really asking

Beneath the experiment framing this is a two-table filter where the cohort lives in one table and the qualifying signal lives in the other. Two things earn a stare. First, `account_status <> 'active'`: under three-valued logic it silently drops any NULL status, and its placement decides whether the join even means what you think. Second, the output has to carry `age_bucket`, which lives in users, so a semi-join subquery will not do here. You actually have to join the two tables and project a users column. The rest is a vanilla inner join with two equality filters and ORDER BY experiments.created.

---

### Break down the requirements

#### Step 1: Filter experiments first

variant='control' AND platform='android' on the 1.5M-row table. Push both predicates down before the join to shrink the probe.

#### Step 2: Inner-join to users

Join on user_id, filter u.account_status <> 'active', and project u.age_bucket into the output. INNER is correct: a missing user row has no age bracket and cannot satisfy the status predicate, so there is nothing to preserve.

---

### The solution

**Radio Silence**

```sql
SELECT e.user_id, u.age_bucket, e.platform, e.variant
FROM experiments e
INNER JOIN users u ON e.user_id = u.user_id
WHERE e.variant = 'control'
  AND e.platform = 'android'
  AND u.account_status <> 'active'
ORDER BY e.created ASC;
```

> **Cost Analysis**
>
> experiments(variant, platform, created) lets the filter and ORDER BY share one index. The join into users(10M) on the user_id PK is one lookup per surviving row, and that same lookup also returns age_bucket.

> **Interviewers Watch For**
>
> Whether you flag NULL handling. NULL <> 'active' is UNKNOWN and excluded; if 'not active' should include NULL accounts, reach for IS DISTINCT FROM instead.

> **Common Pitfall**
>
> Putting `account_status <> 'active'` into the ON clause of a LEFT JOIN keeps unmatched rows with NULL status and re-admits users you meant to exclude. INNER JOIN with the predicate in WHERE avoids it.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- What if a user has multiple experiments rows for the same exp_id? _(One per assignment or one per user: add DISTINCT or aggregate by user_id.)_
- How would you compare inactive rates across control and treatment on android? _(GROUP BY variant, count user_ids with status other than 'active' over totals, then take the ratio.)_
- What index covers this for an hourly run? _(Composite on experiments(variant, platform, created); users.user_id is already the PK.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/gone_to_ground)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.