# Multi-OS Users

> iOS today, Android tomorrow.

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

Domain: SQL · Difficulty: easy · Seniority: L4

## Problem

A downstream consumer flagged missing cross-platform users. Find users who have logged in from devices running different operating systems across sessions. A user qualifies if they have more than one unique OS name. Return all session records for qualifying users, including user ID, OS name, device type, and session start.

## Worked solution and explanation

### Why this problem exists in real interviews

This checks your ability to join two tables, apply a grouped filter using `HAVING`, and then retrieve full detail rows for qualifying groups. It tests the common pattern of identifying qualifying entities in a subquery and then rejoining to get their detail records.

---

### Break down the requirements

#### Step 1: Join sessions to devices

`JOIN devices ON user_sessions.device_id = devices.device_id` brings in `os_name` and `device_type` for each session.

#### Step 2: Identify multi-OS users

In a subquery, `GROUP BY user_id HAVING COUNT(DISTINCT os_name) > 1` finds users with sessions on more than one OS.

#### Step 3: Retrieve full session records

Join the qualifying user IDs back to the session+device data to return `user_id`, `os_name`, `device_type`, and `session_start` for every session of qualifying users.

---

### The solution

**Subquery for qualifying users, then rejoin**

```sql
SELECT s.user_id, d.os_name, d.device_type, s.session_start
FROM user_sessions s
JOIN devices d ON s.device_id = d.device_id
WHERE s.user_id IN (
    SELECT s2.user_id
    FROM user_sessions s2
    JOIN devices d2 ON s2.device_id = d2.device_id
    GROUP BY s2.user_id
    HAVING COUNT(DISTINCT d2.os_name) > 1
)
```

> **Cost Analysis**
>
> With `user_sessions` (40,000,000 rows), `devices` (6,000,000 rows), the full scan reads significant data. A composite index on the filter columns pushes the predicate into the index layer. Pre-aggregation in a materialized view is worth considering at this scale.

> **Interviewers Watch For**
>
> Interviewers evaluate whether you translate the English requirements into the correct SQL clauses on the first attempt. They watch for clean syntax, correct column references, and whether you verify edge cases before declaring the query complete.

> **Common Pitfall**
>
> Using `COUNT(os_name)` instead of `COUNT(DISTINCT os_name)` counts total sessions per OS rather than unique OS values. A user with 100 iOS sessions would count as 100, not 1.

---

## Common follow-up questions

- What happens if the primary filter column contains NULL values? _(Tests NULL awareness in WHERE predicates.)_
- How would you add pagination to this result set? _(Tests OFFSET/LIMIT mechanics and cursor-based alternatives.)_
- What index would you create to speed up this query? _(Tests basic indexing knowledge for the dominant filter/sort column.)_

## Related

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