Mobile vs Desktop Session Duration

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

L4

Asked in

ESPN

Problem

For the 2025 mobile-versus-desktop engagement review, the product analytics team wants each user's longest mobile session beside their longest desktop session, built from user_sessions and devices. A session only counts when its device is listed in devices. Everyone with at least one such session gets a row, even users seen only on tablets, TVs or wearables, and a side a user never used stays empty.

Mobile versus desktop. Who stays longer?

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

This is a two-column pivot dressed up as a device comparison, and the real question is who gets a row. Anyone can write two conditional maximums. What separates candidates is seeing that the link to devices defines the population, not the device type: every user seen on an identified device in 2025 is a row, even one who only ever used a tablet or a TV. Narrow the rows to 'mobile' and 'desktop' and the query returns 8 users instead of 19. The 11 who used neither type vanish when they belong in the answer with both sides empty. Every number you print is right and the row set is still wrong.

Build it in order

Step 1: Restrict to 2025 by session_start

STRFTIME('%Y', s.session_start) = '2025' keeps the calendar year, not a rolling twelve months. The predicate sits on the session side, so it trims rows before they are matched.

Step 2: Inner-join devices on device_id

A NULL device_id equals nothing, and an id with no row in devices has no partner, so both kinds of unidentifiable session drop out here. That is the whole exclusion. Adding a predicate on device_type would be a second, wrong exclusion.

Step 3: Pivot with two conditional maximums per user_id

GROUP BY s.user_id gives one row per user. Each MAX(CASE ...) reads only its own device type, so a phone-only user gets NULL in longest_desktop, and a user whose identified sessions are all 'tablet' or 'wearable' gets NULL in both columns and still keeps the row.

SELECT s.user_id,
    MAX(CASE WHEN d.device_type = 'mobile' THEN s.session_duration_sec END) AS longest_mobile,
    MAX(CASE WHEN d.device_type = 'desktop' THEN s.session_duration_sec END) AS longest_desktop
FROM user_sessions s
JOIN devices d ON s.device_id = d.device_id
WHERE STRFTIME('%Y', s.session_start) = '2025'
GROUP BY s.user_id

The two wrong row sets

SELECT s.user_id,
    MAX(CASE WHEN d.device_type = 'mobile' THEN s.session_duration_sec END) AS longest_mobile,
    MAX(CASE WHEN d.device_type = 'desktop' THEN s.session_duration_sec END) AS longest_desktop
FROM user_sessions s
JOIN devices d ON s.device_id = d.device_id
WHERE STRFTIME('%Y', s.session_start) = '2025'
  AND d.device_type IN ('mobile', 'desktop')
GROUP BY s.user_id

Left join

LEFT JOIN devices keeps a session whose device_id is NULL or missing from devices, with a NULL device_type. A user who has only those sessions comes out as an empty row that the statement excludes.

Inner join

JOIN devices drops those sessions before grouping. A user appears only with at least one identified device, which is exactly the population the statement defines.

Common follow-up questions

• What happens to your result if `devices.browser` contains NULLs for some rows? (Tests whether you see that NULLs in a column you never reference cannot change the join or the aggregate.)
• If `devices` had duplicate `device_id` rows, how would the fan-out affect `MAX` compared with a sum or a count? (Tests join cardinality awareness: a maximum survives duplication, but sums and counts do not.)
• Product now wants only users who used at least one of the two types. Where does that condition go? (Tests seeing that the new condition filters the collapsed per-user row, not the sessions.)