# Top Mobile OS by Session Duration

> Which mobile OS keeps users longest?

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Which mobile operating system has the highest average session duration? Show just the top OS and its average.

## Worked solution and explanation

### Why this problem exists in real interviews

They want to see whether you pin down the mobile filter before you write a line. `devices.device_type` could be `'mobile'`, or mobile could mean `os_name IN ('iOS','Android')`. Pick the wrong column and your answer is silently wrong. Same for `avg_duration`: ask whether they want average per session or total time per OS divided by users.

---

### Break down the requirements

#### Step 1: Confirm the mobile predicate

Ask aloud: is `device_type = 'mobile'` the cohort, or do we treat any `os_name` of iOS or Android as mobile? Brief says `device_type = 'mobile'`. Lock it.

#### Step 2: Join sessions to devices

`user_sessions us INNER JOIN devices d ON us.device_id = d.device_id`. INNER is fine here: a session with no device row is data quality noise, not a mobile session.

#### Step 3: Aggregate average duration

`AVG(us.session_duration_sec)` grouped by `d.os_name`. Average across sessions, not users. Call that out so the interviewer knows you considered the alternative.

#### Step 4: Rank and cap to one row

`ORDER BY avg_duration DESC LIMIT 1`. The prompt says top OS, singular. Don't return a tie-handling rank unless they push.

---

### The solution

**TOP MOBILE OS BY AVG SESSION DURATION**

```sql
SELECT d.os_name,
       AVG(us.session_duration_sec) AS avg_duration
FROM user_sessions us
INNER JOIN devices d
  ON us.device_id = d.device_id
WHERE d.device_type = 'mobile'
GROUP BY d.os_name
ORDER BY avg_duration DESC
LIMIT 1
```

> **Cost Analysis**
>
> 30M sessions joined to 5M devices is a hash join on `device_id`. Push `device_type = 'mobile'` into the build side so the hash table holds only mobile devices, then probe with sessions. If `user_sessions` is partitioned by `session_start`, no pruning helps here since the question is all-time.

> **Interviewers Watch For**
>
> Before SQL, ask two things out loud: (1) does mobile mean `device_type = 'mobile'` or `os_name IN ('iOS','Android')`, and (2) average per session or total minutes per OS. Pick one, name it, write it. Skipping that question is the actual fail.

> **Common Pitfall**
>
> Filtering on `os_name` instead of `device_type` pulls in iOS and Android tablets, plus desktop iOS Simulator traffic if it lands in the table. Your average gets dragged by tablet sessions that run hours long. Use the `device_type` column the schema gave you.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- How would the query change if a single device could have its `os_name` updated over time? _(Probes whether you'd want an SCD2 device dimension or accept current-state attribution.)_
- What if we want average minutes per user per OS instead of per session? _(Tests whether you can switch grain from session to user with a nested aggregation.)_
- Last 30 days only, given the partition on `session_start`, what changes? _(Checks that you add `WHERE us.session_start >= CURRENT_DATE - INTERVAL '30 days'` to prune partitions.)_
- How do you handle sessions with NULL `device_id`? _(Probes your INNER vs LEFT JOIN reasoning and whether you'd surface orphan rates to the PM.)_

## Related

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