# Between Worlds

> iOS today, Android tomorrow.

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

Domain: SQL · Difficulty: easy · Seniority: L4

## Problem

Find the users whose sessions span more than one operating system. Return every session those users logged, with user ID, OS name, device type, and session start.

## Worked solution and explanation

### What they're really testing

This is a unique-value count wearing a cross-platform-audit costume. Everyone gets the join and the grouping right. The whole thing turns on the aggregate you choose: COUNT(os_name) counts sessions, while COUNT(DISTINCT os_name) counts the different operating systems a user actually touched. Miss the DISTINCT and a user with 100 iOS sessions and nothing else looks like a platform-hopper, and your 'multi-OS' set fills up with single-platform users. The second half is the rejoin: HAVING hands you the qualifying user_ids, but the ask wants every session row back, so you filter the full detail set by those users instead of returning the grouped result.

---

### Break down the requirements

#### Step 1: Join sessions to devices

`JOIN devices ON user_sessions.device_id = devices.device_id` brings `os_name` and `device_type` onto each session row so you can reason about the OS a session ran on.

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

In a subquery, `GROUP BY user_id HAVING COUNT(DISTINCT os_name) > 1` keeps only users who touched more than one operating system. The DISTINCT is the load-bearing token here: without it you count sessions, not platforms.

#### Step 3: Retrieve full session records

Filter the joined session and device rows to those qualifying user ids with `WHERE user_id IN (...)`, returning `user_id`, `os_name`, `device_type`, and `session_start` for every session, not one row per user.

---

### The solution

**Find qualifying users, then rejoin for detail**

```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 at scale**
>
> The inner aggregate scans user_sessions (40,000,000 rows) joined to devices (6,000,000 rows) to build the qualifying set, then the outer query rescans to expand detail rows. At this scale a covering index on user_sessions(user_id, device_id) lets the join and the per-user grouping stay index-resident, and a pre-aggregated summary of distinct OS count per user is worth caching if this feeds a dashboard.

> **The tell interviewers wait for**
>
> The single clearest tell is whether COUNT(DISTINCT os_name) shows up without prompting. A candidate who writes COUNT(os_name) and moves on has not internalized that HAVING filters groups on an aggregate, and that the aggregate has to distinguish unique values, not tally rows.

> **The COUNT vs COUNT DISTINCT trap**
>
> COUNT(os_name) counts total sessions per OS instead of the number of different operating systems. A user with 100 iOS sessions and zero others clears a naive '> 1' filter and pollutes the result, even though they never left one platform.

> **Nulls in os_name**
>
> Sessions can reference a device with no recorded os_name. COUNT(DISTINCT os_name) skips nulls, so those sessions never inflate the platform count, but they still come back as detail rows for a user who qualifies on their other sessions. Decide explicitly whether that is the behavior you want.

---

## Common follow-up questions

- How would you rewrite this so the multi-OS flag is computed in a single pass instead of a subquery plus rejoin? _(Tests whether the candidate can move a group predicate onto a running window without a second scan.)_
- If some devices have a null os_name, how does that change both the qualifying set and the returned rows? _(Tests understanding that null os_name is excluded by COUNT(DISTINCT) and how that interacts with the detail rows.)_
- What index would you build to keep the qualifying-user subquery from scanning all 40,000,000 session rows? _(Tests indexing intuition for the dominant join and grouping columns.)_

## Related

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