# Mobile vs Desktop Session Duration

> Mobile versus desktop. Who stays longer?

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

Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: ESPN

## Problem

For each user in 2025, compute the longest session on a mobile device versus the longest session on a desktop device. Consider only sessions that are linked to a known device (a session whose device cannot be identified is excluded). When a user has no session on one of the two device types, leave that side empty (NULL).

## Tables

### user_sessions

- session_id INTEGER
- user_id INTEGER
- device_id INTEGER
- session_start TEXT
- session_duration_sec INTEGER
- pages_viewed INTEGER

Sample rows (10 of 200):

| session_id | user_id | device_id | session_start | session_duration_sec | pages_viewed |
| --- | --- | --- | --- | --- | --- |
| 7061 | 197 | 230 | 2026-02-02 01:00:00 | 53 | 3 |
| 7122 | 294 | 259 | 2026-03-03 02:00:00 | 76 | 6 |
| 7183 | 391 | 288 | 2026-04-04 03:00:00 | 99 | 9 |
| 7244 | 488 | 317 | 2026-05-05 04:00:00 | 122 | 12 |
| 7305 | 585 | 346 | 2026-06-06 05:00:00 | 145 | 15 |
| 7366 | 682 | 375 | 2026-07-07 06:00:00 | 168 | 18 |
| 7427 | 779 | 404 | 2026-08-08 07:00:00 | 191 | 21 |
| 7488 | 876 | NULL | 2026-09-09 08:00:00 | 214 | 24 |
| 7549 | 973 | 462 | 2026-01-10 09:00:00 | 237 | 27 |
| 7610 | 1070 | 491 | 2026-02-11 10:00:00 | 260 | 30 |

### devices

- device_id INTEGER
- device_type TEXT
- os_name TEXT
- os_version TEXT
- browser TEXT

Sample rows (10 of 200):

| device_id | device_type | os_name | os_version | browser |
| --- | --- | --- | --- | --- |
| 201 | desktop | Android | 15.1 | Chrome |
| 230 | tablet | Windows | 16.2 | Firefox |
| 259 | smart_tv | macOS | 17.0 | Edge |
| 288 | wearable | Linux | 11 | Opera |
| 317 | mobile | tvOS | 12 | Samsung Internet |
| 346 | desktop | watchOS | 13 | NULL |
| 375 | tablet | iOS | 10.0.19041 | Safari |
| 404 | smart_tv | Android | 14.1 | Chrome |
| 433 | wearable | Windows | NULL | Firefox |
| 462 | mobile | macOS | 14.0 | Edge |

## Expected output

First 5 of 19 rows:

| user_id | longest_mobile | longest_desktop |
| --- | --- | --- |
| 5144 | 122 | NULL |
| 5435 | NULL | 215 |
| 6502 | 432 | NULL |
| 7472 | 1672 | NULL |
| 8442 | 2912 | NULL |

## Worked solution and explanation

### Why this problem exists in real interviews

By linking `user_sessions`.session_start against devices, this problem requires grouping and date extraction on a multi-table result set. Interviewers deploy it in mid-level screens to see if you handle the intermediate cardinality correctly.

> **Trick to Solving**
>
> Read the prompt carefully for implicit constraints. The phrase structure hints at the grain of the output: what each row represents.
> 
> 1. Identify the output grain from the prompt (one row per what?)
> 2. Work backward from the desired output columns
> 3. Build the query inside-out: innermost subquery first, then layer on filters and aggregates

---

### Break down the requirements

#### Step 1: Join tables with INNER JOIN

Connect `user_sessions` and `devices` on `device_id` to keep only matching rows.

#### Step 2: Filter to the target rows

Apply the date filter using `STRFTIME` to extract and compare the relevant time component. This restricts rows before aggregation.

#### Step 3: Aggregate with MAX

Group by the output grain and apply `MAX()` to compute the metric. The `GROUP BY` must match exactly what the output needs: one row per group key.

---

### The solution

**Conditional MAX for side-by-side comparison**

```sql
SELECT s.user_id,
    MAX(CASE WHEN d.device_type = 'mobile' THEN s.session_duration_sec END) AS longest_mobile,
    MAX(CASE WHEN d.device_type = 'desktop' THEN s.session_duration_sec END) AS longest_desktop
FROM user_sessions s
JOIN devices d ON s.device_id = d.device_id
WHERE STRFTIME('%Y', s.session_start) = '2025'
GROUP BY s.user_id
```

> **Cost Analysis**
>
> The join touches `user_sessions` (60M rows) and `devices` (8M rows). `user_sessions` is partitioned by `session_start`, which the optimizer can exploit with a partition filter.

> **Interviewers Watch For**
>
> Interviewers expect you to articulate why you chose a specific join type and what happens to unmatched rows. Walking through comparison logic step by step, rather than writing it in one pass, demonstrates structured thinking.

> **Common Pitfall**
>
> Forgetting that a JOIN can multiply rows when the relationship is one-to-many. Always check whether the join key is unique on at least one side.

---

## Common follow-up questions

- What happens to your result if devices.browser contains NULLs for some rows? _(Tests whether the candidate accounts for NULL behavior in aggregates and comparisons on browser.)_
- If the join between `user_sessions` and devices produces a fan-out, how does that affect your aggregate? _(Tests awareness of join cardinality and its impact on SUM, COUNT, and AVG results.)_
- With millions of distinct values in `user_sessions`.session_id, what index strategy would you use to keep this query performant? _(Tests indexing knowledge specific to high-cardinality columns like `session_id`.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/mobile_vs_desktop_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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.