User Devices

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

Problem

Sessions repeat heavily, so a user with fifty phone sessions still counts as engaging on one platform: each username and device type pair belongs in the result exactly once. From users, user_sessions and devices, list every device type each user with at least one session has engaged on, next to their username.

Desktop, mobile, tablet. What does each user actually use?

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

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 two-hop lookup through a bridge table that looks like a simple user report. The attribute you need, device_type, does not live on the session fact: user_sessions carries only device_id, so users joined to user_sessions alone can never answer it. Most candidates find the second hop to devices. The real split is what they do with the fan-out. Every session becomes a row, and with roughly 12.5 sessions per user spread over only four device types, the same (user, platform) pair comes back over and over. Skip the dedup and a result of about 60 pairs becomes tens of millions of rows, and any per-user platform count downstream measures session volume instead of platforms.

Getting there

Step 1: Hop from users to user_sessions

Inner-join on user_id. The inner join is the requirement: a user with no sessions has no platform to report, so dropping them is correct, not a side effect.

Step 2: Hop from user_sessions to devices

Join on device_id to reach device_type. This is the hop people skip when they assume the session row knows its platform. It does not, and the query will either fail or invent a column.

Step 3: Collapse the fan-out to one row per pair

Project only u.username and d.device_type, then SELECT DISTINCT over exactly those two columns. Project anything session-level, like session_id or session_start, and the dedup does nothing because every row is unique again.

SELECT DISTINCT u.username, d.device_type
FROM users u
JOIN user_sessions s ON u.user_id = s.user_id
JOIN devices d ON s.device_id = d.device_id

Join without dedup

One row per session: a user with 100 'mobile' sessions shows 'mobile' 100 times, and the output is as large as user_sessions itself.

Join with dedup

DISTINCT on (username, device_type) keeps one row per pair, so the result size tracks users times platforms, not session volume.

Common follow-up questions

• How would you count platforms per user instead of listing them? (Grouping by `u.user_id` with `COUNT(DISTINCT d.device_type)`; grouping by `username` alone breaks if usernames ever repeat.)
• What if users with zero sessions must appear too? (A `LEFT JOIN` chain from `users`, and deciding how a missing `device_type` should be shown.)
• Which users engage on more than one platform? (`HAVING COUNT(DISTINCT d.device_type) > 1` after grouping by user.)