# Exclusive Users per Device Type

> Loyal to one platform only.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

For each device type, count users who have only ever used that single device type across all their sessions. Return the device type and exclusive user count.

## Worked solution and explanation

### Why this problem exists in real interviews

Finding users exclusive to one device type tests set-based reasoning. "Exclusive" means the user's device set has cardinality 1. This probes `GROUP BY` / `HAVING COUNT(DISTINCT ...)` combined with a second aggregation.

> **Trick to Solving**
>
> "Exclusive to" means the user has only ever used one device type. Use `HAVING COUNT(DISTINCT device_type) = 1` to find these users, then count per device type.
> 
> 1. Join sessions to devices for device_type
> 2. Group by user, filter to COUNT(DISTINCT device_type) = 1
> 3. Re-aggregate those users by their single device type

---

### Break down the requirements

#### Step 1: Identify exclusive users

Join `user_sessions` to `devices` on `device_id`. Group by `user_id` with `HAVING COUNT(DISTINCT device_type) = 1`.

#### Step 2: Count per device type

For exclusive users, `MIN(device_type)` gives their single type. Group by it to count.

---

### The solution

**Exclusive user detection with COUNT DISTINCT HAVING**

```sql
WITH exclusive AS (
    SELECT s.user_id, MIN(d.device_type) AS device_type
    FROM user_sessions s
    JOIN devices d ON s.device_id = d.device_id
    GROUP BY s.user_id
    HAVING COUNT(DISTINCT d.device_type) = 1
)
SELECT device_type, COUNT(*) AS exclusive_user_count
FROM exclusive
GROUP BY device_type
ORDER BY exclusive_user_count DESC
```

> **Cost Analysis**
>
> The CTE scans sessions joined to devices once. An index on `devices(device_id, device_type)` and `user_sessions(user_id, device_id)` optimizes both.

> **Interviewers Watch For**
>
> The interviewer checks whether you correctly interpret "exclusive." Filtering `WHERE device_type = 'mobile'` gives all mobile users, not exclusive ones.

> **Common Pitfall**
>
> Confusing "users who used device X" with "users who ONLY used device X." The exclusivity requires checking across all sessions.

---

## Common follow-up questions

- How would you find users using exactly two device types? _(Tests HAVING COUNT(DISTINCT) = 2.)_
- What if device_type can be NULL? _(Tests NULL handling in COUNT DISTINCT.)_
- How would you list each multi-device user's device types? _(Tests GROUP_CONCAT or STRING_AGG.)_

## Related

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