# Top Device by Sessions > One device type generates the most sessions. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 · Asked in: Natera ## Problem We're profiling which hardware our audience leans on most so we can prioritize testing for it, with every user session counting toward the type of device it ran on. Return the single busiest device type alongside its session count. ## 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 1 of 1 rows: | device_type | session_count | | --- | --- | | smart_tv | 37 | ## Worked solution and explanation ### What this really is This is a dimension lookup dressed up as hardware profiling. Session rows carry only a `device_id`; the label you are asked to count by, `device_type`, lives on `devices`, so every session has to be carried over to its type before anything is counted. Everyone writes `COUNT(*)` and `LIMIT 1`. What separates candidates is the grain: group by `device_id` and you crown one physical device with a handful of sessions instead of the busiest category. On this data the lead is a single session (`smart_tv` 37, `desktop` and `tablet` 36), so a sloppy grain or a stray filter flips the winner. --- ### Break down the requirements #### Step 1: Carry each session to its `device_type` `user_sessions` holds only `device_id`; the label lives on `devices`. `INNER JOIN devices d ON us.device_id = d.device_id` attaches it. 23 sessions have a NULL `device_id`, and the inner join drops them because a session with no device has no type. A `LEFT JOIN` would keep them as a NULL group of 23: it loses here, but it is not a device type, so say why you chose inner. #### Step 2: Group on `d.device_type`, not `us.device_id` You want one row per device type (five here), not one per physical device. `COUNT(*)` counts every session, repeat visits by the same user included; `COUNT(DISTINCT us.user_id)` would measure reach, which is a different question. #### Step 3: Sort and keep the top row `ORDER BY session_count DESC LIMIT 1`. Mechanically trivial, semantically loaded: if two types tied for first, `LIMIT 1` would keep whichever the engine reads first. Here `smart_tv` leads by one session, so the cut is clean, but raise the tie question out loud. --- ### 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 ``` > **The join is the whole bill** > > At production scale the 5M-row `devices` dimension is the build side of a hash join and the 30M `user_sessions` rows probe it. The aggregate collapses to a handful of `device_type` groups, so the sort and `LIMIT 1` cost nothing. Partitioning on `session_start` buys nothing because the question spans every session ever logged. > **Ask about ties before you type `LIMIT 1`** > > Ask whether two device types tied at the top should both come back. If yes, `LIMIT 1` is wrong and you need `HAVING COUNT(*) = (SELECT MAX(...) ...)` or a `DENSE_RANK` filter. If one is fine, `LIMIT 1` ships. A one-session lead is exactly when that question earns its keep. > **Group on the label you report** > > Grouping on `us.device_id` and then reaching for `device_type` splits every type across its devices, and the top row becomes one physical device with a few sessions. The group key has to be the thing the answer names. --- ### 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, and tests whether you know `LIMIT 1` silently breaks ties.)_ - What if some `user_sessions.device_id` values are missing or not in `devices`? _(Probes orphan handling: switch to `LEFT JOIN` and bucket them as 'unknown', or keep `INNER JOIN` and say what you dropped.)_ - 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.)_ - How would you make this fast if `user_sessions` were 30B rows instead of 30M? _(Looks for a pre-aggregate: a nightly rollup of `session_count` by `device_type` (or `device_id`) into a small 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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.