Gone to Ground

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
L3
Asked in
Google

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.

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

Tables

experiments

Columns: exp_id INTEGER, exp_name TEXT, variant TEXT, user_id INTEGER, outcome REAL, created TEXT, platform TEXT

experiments: 10 of 200 rows
exp_idexp_namevariantuser_idoutcomecreatedplatform
1053checkout_v3variant_a10011.32026-02-02android
1106onboard_flowvariant_b19722.62026-03-03web
1159pricing_tiervariant_c29433.92026-04-04iOS
1212search_rankholdout39145.22026-05-05Android
1265rec_model_bcontrol48856.52026-06-06NULL
1318notif_freqvariant_a585NULL2026-07-07WEB
1371home_layoutvariant_b68279.12026-08-08NULL
1424new_feed_algovariant_c77990.42026-09-09ios
1477checkout_v3holdout8761.72026-01-10android
1530onboard_flowcontrol973132026-02-11web

users

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

users: 10 of 200 rows
user_idusernameemailsignup_dateaccount_statusage_bucket
100alicealice@example.com2025-02-02inactive25-34
197aaron42aaron42@example.com2026-03-03suspended35-44
294 ameliaamelia@example.com2024-04-04pending_verification45-54
391arjun arjun@example.com2025-05-05active55-64
488ava99ava99@example.com2026-06-06inactive65+
585andrew_kandrew_k@example.com2024-07-07suspendedNULL
682anikaanika@example.com2025-08-08 16:34:00pending_verification18-24
779aidenaiden@example.com2026-09-09 14:53:00active25-34
876aria_baria_b@example.com2024-10-10inactive35-44
973brianbrian@example.com2025-11-11suspended45-54

Expected output

First 4 of 4 rows
user_idage_bucketplatformvariant
630835-44androidcontrol
630835-44androidcontrol
242855-64androidcontrol
242855-64androidcontrol

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, the platform tag arrives in mixed casing, so the Android arm is both 'android' and 'Android'; normalize the case before you match or you silently halve the cohort. Second, account_status <> 'active' has real work to do: the Android-cased control users are active accounts, and this is the predicate that drops them, while under three-valued logic the same predicate silently drops any NULL status, so where you put it decides whether the join means what you think. The output also 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 filters and ORDER BY experiments.created.

Break down the requirements

Step 1: Filter experiments first

variant='control' AND LOWER(platform)='android' on the 1.5M-row table. Normalizing the casing so both 'android' and 'Android' count, then pushing both predicates down before the join, shrinks the probe. Note that LOWER(platform) also excludes the NULL-platform rows, which is what you want.

Step 2: Inner-join to users

Join on user_id, filter u.account_status <> 'active' (this is what removes the active Android-cased control users), 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
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 LOWER(e.platform) = 'android'
  AND u.account_status <> 'active'
ORDER BY e.created ASC;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions