Sessions Per Device Type

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Google

Problem

Our session report only lists device ids, so nobody can see which kinds of devices our traffic actually comes from. Using user_sessions and the devices catalog, count the sessions on each device type, busiest type first.

Sessions, device by device.

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

This is a fact-to-dimension lookup dressed up as a traffic report. user_sessions only knows a device_id; the label the question is about, device_type, lives in devices. Anyone can write COUNT(*). What separates candidates is noticing that the thing you group by is not in the table you count, and resolving it before you aggregate. Group by device_id instead and you hand back 99 rows of one to four sessions each: a per-device tally that never answers the per-type question.

The path to the answer

Step 1: Resolve each session's device_id into a type

Join user_sessions to devices on device_id, so every session carries the device_type of the device it ran on. Because device_id is unique in devices, each session matches at most one device: the join cannot fan out, and the counts stay honest.

Step 2: Count sessions per device_type

Group by d.device_type and take COUNT(*). After the join each row is still exactly one session, so counting rows is counting sessions. Five types come back, one row each, 177 sessions in total.

Step 3: Put the busiest type first

Sort by session_count descending. On this data 'desktop' and 'tablet' both land on 36. Their relative order is not part of the question, and either order grades correct.

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

Grouped by the key

GROUP BY device_id on user_sessions alone: 99 rows, one per device, each with 1 to 4 sessions. The arithmetic is correct and the grain is wrong.

Grouped by the label

Join to devices first, then GROUP BY d.device_type: 5 rows, one per type, the shape the question asked for.

Common follow-up questions

• How would you also show the share of all sessions each device type accounts for? (Tests a total over the grouped rows without a second scan of `user_sessions`.)
• What changes if `devices` keeps one row per device per OS upgrade instead of one row per device? (Probes fan-out: a non-unique dimension key multiplies every session it matches.)
• How would you report the sessions with no `device_id` as an explicit 'unknown' bucket? (Tests an outer join plus a default for the missing label.)