# Device Type Serving Most Users

> One device type serves more users than the rest.

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

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Which device type serves the most unique users based on session data? If there's a tie, include all tied types. Return the device type and user count.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests a top-1-per-group query with tie inclusion. The requirement to include all tied types probes RANK vs ROW_NUMBER selection.

---

### Break down the requirements

#### Step 1: Join sessions to devices

Join on `device_id` to get `device_type`.

#### Step 2: Count unique users per type

`GROUP BY device_type` with `COUNT(DISTINCT user_id)`.

#### Step 3: Find the maximum with ties

Use `RANK()` or compare to `MAX() OVER ()` to find the top type(s).

---

### The solution

**Top-1 with tie inclusion**

```sql
WITH type_users AS (
    SELECT
        d.device_type,
        COUNT(DISTINCT us.user_id) AS user_count
    FROM user_sessions us
    JOIN devices d ON us.device_id = d.device_id
    GROUP BY d.device_type
)
SELECT device_type, user_count
FROM type_users
WHERE user_count = (SELECT MAX(user_count) FROM type_users)
```

> **Cost Analysis**
>
> Join of 60M sessions to 10M devices. COUNT(DISTINCT user_id) per device type requires hash sets per group. The subquery comparison runs on a small set.

> **Interviewers Watch For**
>
> Whether the candidate handles the tie condition. Using LIMIT 1 would drop ties. The `WHERE = MAX` or RANK approach includes all tied types.

> **Common Pitfall**
>
> Using ORDER BY user_count DESC LIMIT 1 would return only one type even if multiple types are tied. The prompt says to include all tied types.

---

## Common follow-up questions

- How would you extend this to the top 3 device types? _(Tests DENSE_RANK with a filter on rank <= 3.)_
- What if you needed this per region? _(Add region to the GROUP BY and PARTITION BY.)_
- What if the join produces no results for a device type? _(That type would not appear. Tests whether a device dimension table is needed.)_

## Related

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