iOS Sessions by Device Type

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

Google

Problem

Each session in user_sessions records only a device_id, while the device's type and operating system are stored in devices. Count the sessions that came from mobile devices, broken down by operating system.

iOS engagement, 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 filter dressed up as a usage report. The thing you count, sessions, lives in user_sessions. The attribute that decides which sessions qualify (device_type) and the label you report them under (os_name) both live in devices. Everyone sees that a join is needed. The trap is answering from the wrong side of it: count rows in devices and you get the mobile device population per operating system, not the sessions those devices started. A phone with four sessions collapses to one, and Android comes out at 3 when the seed holds 6.

Walk the grain

Step 1: Decide what one counted row is

The answer counts sessions, so every counted row must be a user_sessions row. devices is there to label and filter, never to be counted. Saying 'one row per session, tagged with its device' out loud before typing is most of the problem.

Step 2: Attach each session to its device

Join user_sessions to devices on device_id. device_id is unique in devices, so the join cannot multiply sessions. The sessions with a NULL device_id find no partner and drop out, which is right: nothing ties them to a mobile device.

Step 3: Keep mobile, then count per operating system

Put device_type = 'mobile' in WHERE so it removes rows before anything is counted, then group by os_name and count session_id. Seven operating systems come back, and together they account for the 35 mobile sessions.

SELECT d.os_name, COUNT(s.session_id) AS session_count
FROM user_sessions AS s
JOIN devices AS d ON s.device_id = d.device_id
WHERE d.device_type = 'mobile'
GROUP BY d.os_name

SELECT os_name, COUNT(*) AS session_count
FROM devices
WHERE device_type = 'mobile'
GROUP BY os_name

Counting devices

COUNT(*) over devices gives 20 mobile devices spread across the seven operating systems: 3 for Android, 2 for iOS. It never reads user_sessions, so how often a device was used cannot show up.

Counting sessions

COUNT(s.session_id) after the join gives 35 sessions: 6 for Android, 4 for iOS. Each device contributes once per session it started, which is the number a usage report means.

Common follow-up questions

• How would you add each operating system's share of all mobile sessions next to its count? (Tests putting a grand total beside per-group counts without a second scan.)
• What changes if `devices` keeps one row per device per day, so `device_id` repeats? (Tests whether they check join-key uniqueness before trusting a count.)
• The report should now list every operating system, including ones with zero mobile sessions. What moves? (Tests where the mobile filter must sit once unmatched rows are kept.)