# The Screens They Carry

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

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

The product team is profiling how the 25-34 age cohort spreads its usage across hardware. Count the unique devices they use, broken down by device type, from most to fewest.

## 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` puts the most-used kinds on top.

---

### The solution

**Filtered three-table join with a deduped count**

```sql
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
```

> **Cost Analysis**
>
> The plan filters 8M users down to the 25-34 slice first, then probes 40M sessions and 6M devices via hash joins on user_id and device_id. Because the cohort filter lands on the smallest table and cuts it early, the heavy session scan runs against a much smaller build side. The final grouping is over just a handful of device_type values, so the aggregate is trivial.

> **Interviewers Watch For**
>
> Whether the candidate builds the chain correctly: users to sessions on user_id, sessions to devices on device_id. Reaching for a users-to-devices join directly is the tell that they have not noticed sessions is the only thing linking the two.

> **Common Pitfall**
>
> COUNT(*) instead of COUNT(DISTINCT device_id) counts sessions, not devices. One device used across many sessions gets tallied once per session, so every device_type comes back inflated and the ordering can flip.

---

## 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_screens_they_carry)
- [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.