Between Worlds

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

L4

Asked in

ESPN

Problem

A user who moves between device types, a phone one day and a laptop the next, is what the growth team's cross-device review is chasing, and only sessions tied to a known device in devices say anything about that. From user_sessions, return every such session for those users, with the user ID, OS name, device type and session start.

iOS today, Android tomorrow.

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 they're really testing

This is a unique-value count wearing a cross-device costume. Everyone gets the join and the grouping right; the whole problem turns on what you count per user. COUNT(device_type) counts sessions that have a device, while COUNT(DISTINCT device_type) counts the different kinds of device a user actually touched. Almost every user in this session log stays on one device type for every session, so the session tally clears 'more than one' for nearly everybody and the answer balloons from one user's 6 sessions to 169. The second half is the rejoin: the grouping hands you the qualifying user_ids, but the ask wants every session row back.

Break down the requirements

Step 1: Join user_sessions to devices

Inner-join on device_id to put device_type and os_name on every session. A session with a NULL device_id has no device, so the join drops it, which is exactly what 'tied to a known device' asks for.

Step 2: Find the cross-device users

Group the joined rows by user_id and keep HAVING COUNT(DISTINCT device_type) > 1. The DISTINCT decides the answer here: without it you count sessions, not kinds of device.

Step 3: Bring back every session

Filter the joined rows with WHERE user_id IN (...) and return user_id, os_name, device_type and session_start, one row per session, not one per user.

The solution

SELECT s.user_id, d.os_name, d.device_type, s.session_start
FROM user_sessions s
JOIN devices d ON s.device_id = d.device_id
WHERE s.user_id IN (
    SELECT s2.user_id
    FROM user_sessions s2
    JOIN devices d2 ON s2.device_id = d2.device_id
    GROUP BY s2.user_id
    HAVING COUNT(DISTINCT d2.device_type) > 1
)

Counting sessions

HAVING COUNT(d2.device_type) > 1 is true for any user with two device-linked sessions. Nearly every user qualifies and you return 169 sessions.

Counting device types

HAVING COUNT(DISTINCT d2.device_type) > 1 is true only when two sessions ran on different kinds of device. One user qualifies and you return their 6 sessions.

Common follow-up questions

• How would you compute the cross-device flag in a single pass instead of a subquery plus rejoin? (Tests knowing `COUNT(DISTINCT ...)` is not a window aggregate in most engines, while comparing `MIN(device_type)` and `MAX(device_type)` per user is.)
• The review now wants users who used both a 'mobile' and a 'desktop' device specifically. What changes? (Tests moving from a unique count to conditional checks per device type.)
• What index would keep the qualifying-user subquery from scanning all 40,000,000 session rows? (Tests indexing intuition for the dominant join and grouping columns.)