Users 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

Walmart

Problem

Our user_sessions log records the device behind each session, but some sessions point at a device that is missing from the devices catalog or name no device at all. For each device type, count how many different users had a session on it, and keep the sessions we can't tie to a known device together as one group with no device type.

Users per device. The split.

Tables

devices

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

user_sessions

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

Expected output

Worked solution and explanation

The real problem hiding in a simple count

This looks like a tidy count of users per device type, but the real test is what happens to the sessions you cannot place. Attributing a fact table to a dimension is an outer join anchored on user_sessions, not on devices: some session rows carry a device_id that is null or points at a device absent from devices. Reach for an inner join, or anchor the join on devices, and every one of those sessions silently vanishes, taking its users with it, so nobody sees the logging gap. Anyone can group and count; the tell is preserving the unmatched sessions as their own group and using COUNT(DISTINCT s.user_id) so a user with fifty sessions counts once.

Walking through it

Step 1: Anchor the join on sessions

Start FROM user_sessions s and LEFT JOIN devices d ON s.device_id = d.device_id. Direction is the whole point: user_sessions is the fact table whose every row must survive, so it is the left side. A session whose device_id is null or missing from devices still comes through, with d.device_type returning as null.

Step 2: Group and count unique users

GROUP BY d.device_type folds every matched session into its type and collapses every unmatched session into a single null-device_type group. COUNT(DISTINCT s.user_id) then dedupes repeat sessions so each person is counted once per group.

The solution

SELECT d.device_type, COUNT(DISTINCT s.user_id) AS user_count
FROM user_sessions s
LEFT JOIN devices d ON s.device_id = d.device_id
GROUP BY d.device_type

Common follow-up questions

• How would you turn the counts into each device type's share of all users? (Divide each group's `COUNT(DISTINCT s.user_id)` by the total distinct `user_id` across every session.)
• What if you needed the same breakdown by `os_name`? (Group by `d.os_name` instead; the null bucket now holds sessions with no matched device row.)
• How would you keep only device types used by more than a million users? (Add `HAVING COUNT(DISTINCT s.user_id) > 1000000` after the `GROUP BY`.)