Platform Check

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

Problem

Which device type and operating system did each session in user_sessions that lasted more than a thousand seconds run on, going by the devices table? Show each such session's user and its duration alongside them.

When a session drags on, the platform it ran on is the first suspect.

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 this really is

Strip off the profiling costume and this is a fact-to-dimension lookup. The filter and the reported numbers live on user_sessions, but the two attributes the question asks for, device_type and os_name, exist only on devices. No computation over the session table can conjure them, so the two tables have to meet on device_id. Everyone writes that join. What separates candidates is what they do about sessions with no device: 23 of the 200 sessions carry a NULL device_id, and a nervous candidate reaches for a LEFT JOIN so they don't 'lose rows'. That emits long sessions with a NULL device type and a NULL operating system, rows that cannot answer 'which device', and the result grows past the 102 rows the grade expects.

Break it down

Step 1: Keep only the long sessions

WHERE s.session_duration_sec > 1000 is strictly greater than, so a session of exactly 1000 seconds stays out. A NULL session_duration_sec fails the comparison too: a session of unknown length is not a long one.

Step 2: Look up each session's device

JOIN devices d ON s.device_id = d.device_id goes from the many side to the one side. device_id is unique in devices, so no session row multiplies. A NULL device_id equals nothing, so those sessions fall away, which is exactly right when the question is which device a session ran on.

Step 3: Return the four columns

Project user_id, device_type, os_name and session_duration_sec. There is no grouping and no aggregate: each qualifying session is its own row.

The solution

SELECT s.user_id, d.device_type, d.os_name, s.session_duration_sec
FROM user_sessions s
JOIN devices d ON s.device_id = d.device_id
WHERE s.session_duration_sec > 1000

Keep every long session

LEFT JOIN devices keeps the long sessions whose device_id is NULL and returns them with NULL device_type and NULL os_name. The row count climbs past 102, and every extra row says nothing about a device.

Keep sessions with a known device

JOIN devices keeps only sessions that matched a device. Every row names a device type and an operating system, which is the only kind of row the question can use.

Common follow-up questions

• Now keep every long session even when its `device_id` is missing or unmatched, showing NULL for device type and operating system. What changes in the query? (Tests whether the candidate can switch to a left join and reason about the NULL device columns it introduces.)
• Suppose the question became the average session length for each device-type and operating-system pair among long sessions. How would the query change? (Tests moving from per-row output to aggregation once the requirement calls for it.)
• If this ran as a scheduled job, how would you alert when the long-session count spikes or drops to zero? (Tests operational awareness around scheduled queries and result-set monitoring.)