The Screens They Carry

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

Airbnb

Problem

Each user_sessions row records which device a user was on, with the user's age group kept in users and the device's type in devices. Count the unique devices the 25-34 age group has used, by device type, from most to fewest, with equal counts listed alphabetically by type.

One age cohort, and every kind of screen it reaches for.

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

Beneath the device-diversity framing, this is a fan-in with a dedup at the far end. A user has many sessions, each session points at exactly one device, and the same device reappears across many sessions. The skill being probed: can you walk the bridge from users through user_sessions to devices, then collapse the fan-out so each physical device is counted once? Anyone can write the three joins. The trap is the aggregate: COUNT(*) counts session rows, not devices, and since one popular device generates many sessions it silently inflates every device_type. COUNT(DISTINCT device_id) is the whole game.

Walking the chain

Step 1: Scope the cohort

WHERE u.age_bucket = '25-34' scopes the population to the cohort of interest before any counting. Applying it on the users table keeps the filter cheap and lets the optimizer prune early.

Step 2: Join through the bridge

There is no direct users-to-devices link. Go users -> user_sessions on user_id, then user_sessions -> devices on device_id. The session table is the bridge that ties a person to the hardware they actually used; skipping it has no join key to stand on.

Step 3: Dedup as you count

GROUP BY d.device_type with COUNT(DISTINCT d.device_id) buckets the surviving rows by kind of device and dedups within each bucket, so a device used in fifty sessions still counts as one. ORDER BY device_count DESC, device_type puts the most-used kinds on top and lists equal counts alphabetically.

The solution

SELECT d.device_type, COUNT(DISTINCT d.device_id) AS device_count
FROM users u
JOIN user_sessions us ON u.user_id = us.user_id
JOIN devices d ON us.device_id = d.device_id
WHERE u.age_bucket = '25-34'
GROUP BY d.device_type
ORDER BY device_count DESC, device_type

Common follow-up questions

• How would you produce this for every age bucket at once? (Drop the `age_bucket` predicate and add `age_bucket` to the `GROUP BY` to get a per-bucket breakdown.)
• How would you show each type's share of the total? (Wrap the count: `100.0 * COUNT(DISTINCT d.device_id) / SUM(COUNT(DISTINCT d.device_id)) OVER ()`.)
• What changes if one device can belong to more than one user? (`COUNT(DISTINCT device_id)` already handles it: a device shared across users is still one device.)