# Devices Per Age Bucket

> Device diversity among the younger users.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

For active users in the 25-34 age bucket, the product team wants to know which device types they use most. Count the number of unique devices broken down by device type, from most to fewest.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests a multi-table JOIN chain with filtering. Connecting users to devices through sessions requires joining three tables and applying filters at multiple levels.

---

### Break down the requirements

#### Step 1: Filter to active users in 25-34 bucket

`WHERE u.account_status = 'active' AND u.age_bucket = '25-34'`.

#### Step 2: Join users to sessions to devices

users -> user_sessions (on user_id) -> devices (on device_id).

#### Step 3: Count unique devices by type

`GROUP BY d.device_type` with `COUNT(DISTINCT d.device_id)`.

---

### The solution

**Three-table join with filtered aggregation**

```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.account_status = 'active'
  AND u.age_bucket = '25-34'
GROUP BY d.device_type
ORDER BY device_count DESC
```

> **Cost Analysis**
>
> Three-way join: 8M users (filtered to a subset) -> 40M sessions -> 6M devices. The user filter reduces the first table significantly. Hash joins on user_id and device_id.

> **Interviewers Watch For**
>
> Whether the candidate constructs the join chain correctly: users to sessions (user_id), sessions to devices (device_id). Joining users directly to devices would skip the session linkage.

> **Common Pitfall**
>
> Using COUNT(*) instead of COUNT(DISTINCT device_id) would count sessions per device type, not unique devices. A single device used in many sessions would be overcounted.

---

## Common follow-up questions

- What if you needed this breakdown for all age buckets? _(Remove the age_bucket filter and add it to GROUP BY.)_
- How would you also show the percentage of devices per type? _(Window function: 100.0 * COUNT(DISTINCT d.device_id) / SUM(COUNT(DISTINCT d.device_id)) OVER ().)_
- What if a device could belong to multiple users? _(COUNT(DISTINCT) on device_id handles this correctly; it counts each device once regardless.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/devices_per_age_bucket)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.