# Users Per Device Type

> Users per device. The split.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

For each device type, count users who have at least one session. Include device types with zero users if applicable. Return the device type and user count.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests a two-table join with GROUP BY and DISTINCT counting. Interviewers check whether you can link sessions to devices and count unique users per device type while handling potential zero-user types.

---

### Break down the requirements

#### Step 1: Join devices to sessions

`LEFT JOIN user_sessions ON devices.device_id = user_sessions.device_id` preserves all device types, including those with zero sessions.

#### Step 2: Count distinct users per device type

`GROUP BY device_type` with `COUNT(DISTINCT user_id)` counts unique users. For device types with no sessions, the count is 0.

---

### The solution

**Left join with distinct user count per device type**

```sql
SELECT d.device_type, COUNT(DISTINCT s.user_id) AS user_count
FROM devices d
LEFT JOIN user_sessions s ON d.device_id = s.device_id
GROUP BY d.device_type
```

> **Cost Analysis**
>
> The LEFT JOIN between 6M devices and 40M sessions produces up to 40M rows. The GROUP BY reduces to 4 device types. An index on `user_sessions(device_id)` is important for join performance.

> **Interviewers Watch For**
>
> Using LEFT JOIN to include device types with zero users. If you use INNER JOIN, device types with no sessions would be silently dropped.

> **Common Pitfall**
>
> Using `COUNT(s.user_id)` instead of `COUNT(DISTINCT s.user_id)`. Without DISTINCT, a user with 50 sessions on the same device type is counted 50 times.

---

## Common follow-up questions

- How would you show the percentage of users per device type? _(Divide each count by the total distinct users across all types.)_
- What if you needed to count by OS instead of device type? _(Change GROUP BY to d.os_name.)_
- How would you find device types used by more than 1 million users? _(Add HAVING COUNT(DISTINCT s.user_id) > 1000000.)_

## Related

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