# The Upgrade Divide > The install numbers don't match the hype. Canonical URL: 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. ## Tables ### users - user_id INTEGER - username TEXT - email TEXT - signup_date TEXT - account_status TEXT - age_bucket TEXT Sample rows (10 of 200): | user_id | username | email | signup_date | account_status | age_bucket | | --- | --- | --- | --- | --- | --- | | 100 | alice | alice@example.com | 2025-02-02 | inactive | 25-34 | | 197 | aaron42 | aaron42@example.com | 2026-03-03 | suspended | 35-44 | | 294 | amelia | amelia@example.com | 2024-04-04 | pending_verification | 45-54 | | 391 | arjun | arjun@example.com | 2025-05-05 | active | 55-64 | | 488 | ava99 | ava99@example.com | 2026-06-06 | inactive | 65+ | | 585 | andrew_k | andrew_k@example.com | 2024-07-07 | suspended | NULL | | 682 | anika | anika@example.com | 2025-08-08 16:34:00 | pending_verification | 18-24 | | 779 | aiden | aiden@example.com | 2026-09-09 14:53:00 | active | 25-34 | | 876 | aria_b | aria_b@example.com | 2024-10-10 | inactive | 35-44 | | 973 | brian | brian@example.com | 2025-11-11 | suspended | 45-54 | ### devices - device_id INTEGER - device_type TEXT - os_name TEXT - os_version TEXT - browser TEXT Sample rows (10 of 200): | device_id | device_type | os_name | os_version | browser | | --- | --- | --- | --- | --- | | 201 | desktop | Android | 15.1 | Chrome | | 230 | tablet | Windows | 16.2 | Firefox | | 259 | smart_tv | macOS | 17.0 | Edge | | 288 | wearable | Linux | 11 | Opera | | 317 | mobile | tvOS | 12 | Samsung Internet | | 346 | desktop | watchOS | 13 | NULL | | 375 | tablet | iOS | 10.0.19041 | Safari | | 404 | smart_tv | Android | 14.1 | Chrome | | 433 | wearable | Windows | NULL | Firefox | | 462 | mobile | macOS | 14.0 | Edge | ### user_sessions - session_id INTEGER - user_id INTEGER - device_id INTEGER - session_start TEXT - session_duration_sec INTEGER - pages_viewed INTEGER Sample rows (10 of 200): | session_id | user_id | device_id | session_start | session_duration_sec | pages_viewed | | --- | --- | --- | --- | --- | --- | | 7061 | 197 | 230 | 2026-02-02 01:00:00 | 53 | 3 | | 7122 | 294 | 259 | 2026-03-03 02:00:00 | 76 | 6 | | 7183 | 391 | 288 | 2026-04-04 03:00:00 | 99 | 9 | | 7244 | 488 | 317 | 2026-05-05 04:00:00 | 122 | 12 | | 7305 | 585 | 346 | 2026-06-06 05:00:00 | 145 | 15 | | 7366 | 682 | 375 | 2026-07-07 06:00:00 | 168 | 18 | | 7427 | 779 | 404 | 2026-08-08 07:00:00 | 191 | 21 | | 7488 | 876 | NULL | 2026-09-09 08:00:00 | 214 | 24 | | 7549 | 973 | 462 | 2026-01-10 09:00:00 | 237 | 27 | | 7610 | 1070 | 491 | 2026-02-11 10:00:00 | 260 | 30 | ## Expected output First 5 of 6 rows: | age_bucket | upgraded_users | total_users | | --- | --- | --- | | 18-24 | 0 | 8 | | 25-34 | 0 | 7 | | 35-44 | 1 | 8 | | 45-54 | 1 | 9 | | 65+ | 4 | 9 | ## 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. > **Trick to solving** > > Do not run two queries. A single COUNT(DISTINCT CASE WHEN THEN `user_id` END) gives the upgraded slice, and COUNT(DISTINCT `user_id`) over the same grouped rows gives the denominator. Reach devices with a LEFT join so a session with no recorded device still counts its user in the total, and both numbers read the same joined rows once, guaranteed to line up per group. --- ### 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. **Conditional count over a three-table join** ```sql 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 pitfall** > > Testing `os_version` alone without also checking `os_name`. Major version numbers are shared across platforms, so a Windows 16.2 or a macOS 17.0 device sails into your upgrade count. The version predicate narrows the release; the `os_name` group is what restricts it to phones. > **The denominator trap** > > Reaching devices with an inner join quietly drops every user whose only session had no recorded device, shrinking the total and making adoption look higher than it is. The device is needed to read the OS, not to prove the session happened, so a LEFT join keeps those users in `total_users` while they stay out of the upgraded slice. Get this wrong and buckets that really tie at the top stop tying, and the whole ordering shifts. > **Interviewers watch for** > > Whether you catch the session fan-out and the device-less sessions before anyone points them out. Saying 'a user has many sessions, so I need DISTINCT' and 'some sessions have no device, so a LEFT join keeps them in the total' out loud is the tell that you think in grains, not just syntax. The other tell is defining the slice precisely, a mobile OS name plus a version test, instead of pattern matching a version number and hoping. > **Cost at scale** > > `user_sessions` holds 50M rows and is partitioned by `session_start`, so a production version of this query usually lands a date filter to prune partitions before the join. The two COUNT(DISTINCT ...) aggregates are the costly part, and both fold into the single GROUP BY pass, so you pay for one aggregation rather than two separate scans. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_upgrade_divide) - [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.