The Upgrade Divide

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

Apple

Problem

Our growth team wants to know how far the latest phone releases, iOS or Android 16, 17, and 18, have spread across our age groups. For each age group, show the number of users who ran a session on one of those releases next to the total number of users with any session.

The install numbers don't match the hype.

Tables

users

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

devices

Columns: device_id INTEGER, device_type TEXT, os_name TEXT, os_version TEXT, browser TEXT

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

Expected output

Worked solution and explanation

What this really is

This is a conditional segment count wearing an adoption-metrics costume. The real question: can you put a filtered count and a grand total side by side, over the same groups, in one pass, without letting the session table double count anyone? Three things separate the strong answers. First, a user shows up once per session, so a plain COUNT inflates every bucket: you need COUNT(DISTINCT user_id). Second, 'on a recent release' means a mobile OS name AND a version 16/17/18, because version strings like '16.2' also live on Windows, macOS and Linux rows: match the version alone and desktop platforms leak straight into your number. Third, the total is every user who logged a session, even one whose device we never captured, so you reach devices with a LEFT join and keep those users in the denominator instead of silently dropping them.

Building it

Step 1: Reach devices through sessions

You can only read os_name and os_version by going through user_sessions, which links a user_id to a device_id. That path is one-to-many: a user with forty sessions becomes forty rows here. Hold onto that fact, because it is the source of every double count in this problem.

Step 2: Count the upgraded slice and the total together

COUNT(DISTINCT u.user_id) collapses the session fan-out back to real people. For the upgraded slice, wrap the same idea in a CASE that returns user_id only when the device is a phone platform, (d.os_name = 'iOS' OR d.os_name = 'Android'), AND its os_version starts with 16, 17, or 18; the DISTINCT then counts each qualifying user once even when they ran several matching sessions. Prefix matching with LIKE '16%' catches '16.2' and '16.0.1' without hard coding every point release. Mind the parentheses: the OS group and the version group are each an OR, joined by AND, so both must be wrapped or precedence quietly widens the match. Join devices with a LEFT join: a session whose device_id we never captured contributes nothing to the upgraded count but still keeps its user in the total.

Step 3: Drop the missing bucket, then order

Rows with a null age_bucket are not an age group, so filter them in WHERE before grouping. Because you reach the buckets through the inner join to user_sessions, only groups with at least one session survive, which is exactly the requirement. Then order by total_users descending, largest groups first, and break ties on age_bucket descending so the order is stable and matches the preview.

SELECT u.age_bucket,
    COUNT(DISTINCT CASE
        WHEN (d.os_name = 'iOS' OR d.os_name = 'Android')
         AND (d.os_version LIKE '16%' OR d.os_version LIKE '17%' OR d.os_version LIKE '18%')
        THEN u.user_id
    END) AS upgraded_users,
    COUNT(DISTINCT u.user_id) AS total_users
FROM users u
JOIN user_sessions s ON u.user_id = s.user_id
LEFT JOIN devices d ON s.device_id = d.device_id
WHERE u.age_bucket IS NOT NULL
GROUP BY u.age_bucket

Version only (wrong)

COUNT(DISTINCT CASE WHEN d.os_version LIKE '16%' OR ... THEN u.user_id END). Counts every platform that ever shipped a version 16, so Windows, macOS and Linux users inflate the mobile-upgrade number.

Mobile OS plus version (right)

COUNT(DISTINCT CASE WHEN (d.os_name = 'iOS' OR d.os_name = 'Android') AND (d.os_version LIKE '16%' OR ...) THEN u.user_id END). Scopes the count to phone platforms on the target releases, which is what upgrade adoption actually means.

Common follow-up questions

• How would you also report the upgraded share as a percentage of each group? (Tests turning two counts into a ratio with a guard against divide by zero.)
• The team now cares only about version 18. How does the CASE change? (Tests tightening the version predicate without disturbing the denominator.)
• `user_sessions` is 50M rows and growing. How do you keep this cheap? (Tests partition pruning and pre-aggregation awareness at scale.)
• How would you validate the upgraded counts on a fresh dataset? (Tests verification instinct: spot checks and reconciliation against a known figure.)