# The Faithful Few

> Loyal to one platform only.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

We track sessions across many devices for a multi-platform product. For each device type, count the users who have only ever used that single device type across all of their sessions, and return the device type with that exclusive user count, most loyal type first.

## Worked solution and explanation

### What this problem really is

Strip the costume and this is a per-user set-cardinality test wearing a product-analytics disguise. A user is loyal to a platform only when their whole distinct device_type set has size one. Anyone can filter rows to `device_type = 'mobile'`; the separation is realizing that a row filter answers 'who ever used mobile', never 'who used mobile and nothing else'. Get that wrong and your loyal count silently absorbs every multi-device user, inflating each platform.

> **Trick to Solving**
>
> 'Exclusive to' means the user's distinct device_type set has cardinality one. Isolate those users with `HAVING COUNT(DISTINCT device_type) = 1`, then re-count them by their single type. A WHERE clause cannot express this because the constraint lives on the group, not the row.

---

### Break down the requirements

#### Step 1: Identify exclusive users

Join user_sessions to devices and group by user_id, keeping only users whose sessions span exactly one distinct device_type via HAVING COUNT(DISTINCT device_type) = 1. The inner join also makes null device_id sessions fall out for free, so they neither add a type nor disqualify a user.

#### Step 2: Count per device type

Each surviving user has exactly one type, so MIN(device_type) reads it back unambiguously. Collapse to one row per exclusive user, then COUNT(*) those rows per device_type. Filtering per user BEFORE this second aggregation is what keeps multi-device users out of the totals.

---

### 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, device_type ASC
```

> **Cost Analysis**
>
> The CTE scans sessions joined to devices once, then the outer query aggregates a tiny per-user intermediate. An index on `devices(device_id, device_type)` and `user_sessions(user_id, device_id)` keeps the join and the per-user grouping cheap even at 60M sessions.

> **Interviewers Watch For**
>
> The tell is whether you constrain the group or the row. `WHERE device_type = 'mobile'` gives every mobile user; only `HAVING COUNT(DISTINCT device_type) = 1` proves exclusivity. Reaching for HAVING here signals you think in sets, not rows.

> **Common Pitfall**
>
> Confusing 'users who used device type X' with 'users who ONLY used device type X'. Exclusivity is a property of the user's entire session history, so it can only be decided after grouping all of their sessions together.

---

## Common follow-up questions

- How would you find users using exactly two device types? _(Tests HAVING COUNT(DISTINCT) = 2.)_
- What if device_type itself could 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/the_faithful_few)
- [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.