# Top Device by Sessions

> One device type generates the most sessions.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

Which device type generates the most user sessions? Show just the top device type and its session count.

## Worked solution and explanation

### Why this problem exists in real interviews

Warm-up question that screens for join direction, aggregate placement, and whether you respect cardinality. The interviewer wants 30M `user_sessions` joined to the 5M `devices` dim, grouped on `d.device_type`, sorted, top 1. Ask about ties before typing `LIMIT 1`.

---

### Break down the requirements

#### Step 1: Join sessions to devices

`device_type` lives on `devices`, the session count lives on `user_sessions`. `INNER JOIN devices d ON us.device_id = d.device_id`. Inner is correct only if you are fine dropping sessions whose `device_id` does not resolve, flag that to the interviewer.

#### Step 2: Group on the dim column

`GROUP BY d.device_type`, not `us.device_id`. You want one row per device type (roughly 4 or 5 values), not one row per physical device. `COUNT(*)` over sessions, since you already filtered to sessions by joining from `user_sessions`.

#### Step 3: Sort and take one

`ORDER BY session_count DESC LIMIT 1`. Trivial mechanically, dangerous semantically. If two device types tie for first, `LIMIT 1` returns whichever the planner picks. Mention this out loud even if you keep `LIMIT 1` in the final answer.

---

### The solution

**TOP DEVICE BY SESSIONS**

```sql
SELECT
  d.device_type,
  COUNT(*) AS session_count
FROM user_sessions us
INNER JOIN devices d ON us.device_id = d.device_id
GROUP BY d.device_type
ORDER BY session_count DESC
LIMIT 1
```

> **Cost Analysis**
>
> 5M `devices` is the build side of a hash join, fits in memory; 30M `user_sessions` is the probe. Partitioning on `session_start` does not help, the question is lifetime. The group by collapses to a handful of `device_type` values, so the aggregate is trivial. JOIN dominates runtime.

> **Interviewers Watch For**
>
> Before writing, ask: `if two device types tie at the top, do you want both?` If yes, `LIMIT 1` is wrong and you need `HAVING COUNT(*) = (SELECT MAX(c) FROM ...)` or a `DENSE_RANK` filter. If they say `pick one, it is fine`, then `LIMIT 1` ships.

> **Common Pitfall**
>
> Grouping on `us.device_id` then trying to surface `device_type`. Same `device_type` will be split across millions of `device_id` rows, and the top one will be a single physical device, not the top type. The group key has to match the answer you are reporting.

---

### COMMON FOLLOW-UP QUESTIONS

## Common follow-up questions

- Rewrite this to return all device types that tie for first. _(Forces `HAVING COUNT(*) = (SELECT MAX(cnt) ...)` or a `DENSE_RANK` filter. Tests whether you understand `LIMIT 1` silently breaks ties.)_
- What if some `user_sessions.device_id` rows are not in `devices`? _(Probes whether you spotted orphan-handling: switch to `LEFT JOIN` and bucket nulls as `unknown`, or keep `INNER JOIN` and acknowledge the drop.)_
- Top device type per month. _(Adds `DATE_TRUNC('month', session_start)` to the group key, then a `DENSE_RANK() OVER (PARTITION BY month ORDER BY COUNT(*) DESC)` filter. Tests window functions over aggregates.)_
- How would you make this fast if `user_sessions` were 30B rows instead of 30M? _(Looks for a pre-aggregate: nightly rollup of `session_count` by `device_type` (or `device_id`) into a small fact table.)_

## Related

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