# User Devices

> Desktop, mobile, tablet. What does each user actually use?

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

The personalization team wants to know which platforms each user actually engages on. For every user with at least one session, show the distinct device types they've used alongside their username.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests a three-table join with deduplication. Interviewers check whether you can chain joins correctly and use DISTINCT to avoid duplicate device types per user.

---

### Break down the requirements

#### Step 1: Join users to sessions

`JOIN user_sessions ON users.user_id = user_sessions.user_id` links users to their session records.

#### Step 2: Join sessions to devices

`JOIN devices ON user_sessions.device_id = devices.device_id` enriches sessions with device type information.

#### Step 3: Select distinct user/device type pairs

`SELECT DISTINCT u.username, d.device_type` deduplicates so each user-device combination appears once.

---

### The solution

**Three-table join with deduplication**

```sql
SELECT DISTINCT u.username, d.device_type
FROM users u
JOIN user_sessions s ON u.user_id = s.user_id
JOIN devices d ON s.device_id = d.device_id
```

> **Cost Analysis**
>
> The first join produces 50M rows (sessions matched to users). The second join enriches with device type from 6M devices. DISTINCT then deduplicates. Indexes on `user_sessions(user_id)` and `devices(device_id)` are essential.

> **Interviewers Watch For**
>
> Whether you use INNER JOIN (only users with sessions) since the prompt says "every user with at least one session." LEFT JOIN would include sessionless users, which is not required here.

> **Common Pitfall**
>
> Forgetting DISTINCT. A user with 100 mobile sessions would appear 100 times with device_type 'mobile' without deduplication.

---

## Common follow-up questions

- How would you count devices per user instead of listing them? _(Replace DISTINCT with GROUP BY u.username and COUNT(DISTINCT d.device_type).)_
- What if you needed to include users with zero sessions? _(Switch to LEFT JOIN from users and handle NULLs in the device column.)_
- How would you find users who use multiple device types? _(GROUP BY u.user_id HAVING COUNT(DISTINCT d.device_type) > 1.)_

## Related

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