Top Device by Sessions

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

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.

One device type generates the most sessions.

Tables

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

devices

Columns: device_id INTEGER, device_type TEXT, os_name TEXT, os_version TEXT, browser TEXT

Expected output

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

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

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.)