# Sessions Per Device Type

> Sessions, device by device.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

How many sessions occurred on each device type? Show the counts from most to fewest.

## Worked solution and explanation

### Why this problem exists in real interviews

The core skill being tested is self-join, applied to the `user_sessions` and `devices` tables in a session analysis context. Getting the `device_type` column right is where most candidates slip.

---

### Break down the requirements

#### Step 1: Join `user_sessions` to `devices`

The join connects the two tables on their shared key. This brings the columns needed for filtering and aggregation into a single row set.

#### Step 2: Aggregate by `d.device_type`

`GROUP BY d.device_type` collapses rows to one per group. The aggregate functions (`SUM`, `COUNT`, `AVG`, etc.) compute the metric for each group.

#### Step 3: Sort the final output

The `ORDER BY` clause ensures the result appears in the expected sequence. Interviewers check that the sort direction matches the prompt.

---

### The solution

**Join `user_sessions` to `devices` to find sessions per device type**

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

> **Cost Analysis**
>
> With ~35M rows, the GROUP BY reduces the working set before any downstream operations; the join cost depends on the smaller table's cardinality. An index on the filter/join columns would reduce the scan to a seek.

> **Interviewers Watch For**
>
> Interviewers watch for whether the query returns exactly the columns and ordering the prompt specifies; how quickly you identify the core operation and write clean, minimal code.

> **Common Pitfall**
>
> Returning extra columns that the prompt did not ask for, or using the wrong column alias, causes a grading mismatch even when the logic is correct.

---

## Common follow-up questions

- If `browser` in `devices` is NULL for some rows, how would your aggregation or join logic be affected? _(Probes understanding of NULL propagation through joins and aggregate functions on `devices.browser`.)_
- If `user_sessions` grew to contain billions of rows, which part of your query would become the bottleneck given the cardinality of `user_id`? _(Tests ability to identify performance hotspots related to `user_sessions.user_id` at scale.)_
- `devices.os_name` only has 5 distinct values. If a new category were added, would your query automatically include it? _(Tests whether the query hard-codes values or dynamically adapts to `os_name` changes.)_

## Related

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