Where Users Linger

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

Airbnb

Problem

An analyst sizing up mobile engagement counts only the sessions in user_sessions whose device appears in devices with a mobile device type, crediting each session to that device's operating system. Among those sessions, return the one operating system with the longest average session duration, together with that average.

Attention is the scarcest resource. Find the platform that holds it longest.

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

Why this problem exists in real interviews

This is a filtered top-1 dressed up as an engagement question. Anyone can write the join, the AVG and the LIMIT 1. The trap is the cohort: mobile is a column, device_type, not a guess about which os_name values sound like phones. Filter on os_name IN ('iOS','Android') and you drop the macOS rows this data files as mobile, which is exactly the OS that wins, so you hand back a plausible runner-up with a confident average.

Break down the requirements

Step 1: Confirm the mobile predicate

Ask aloud: is device_type = 'mobile' the cohort, or do we treat any os_name of iOS or Android as mobile? The brief says mobile device type, which is the device_type column. Lock it.

Step 2: Join user_sessions to devices

user_sessions us INNER JOIN devices d ON us.device_id = d.device_id. INNER is fine here: a session with no device row has no operating system to credit, so it is data quality noise, not a mobile session.

Step 3: Aggregate average duration

AVG(us.session_duration_sec) grouped by d.os_name. Average across sessions, not users, and versions roll up into one row per OS. Call that out so the interviewer knows you considered the alternative.

Step 4: Sort and cap to one row

ORDER BY avg_duration DESC LIMIT 1. The prompt says the one operating system, singular. Don't build a tie-handling rank unless they push.

The solution

SELECT d.os_name,
       AVG(us.session_duration_sec) AS avg_duration
FROM user_sessions us
INNER JOIN devices d
  ON us.device_id = d.device_id
WHERE d.device_type = 'mobile'
GROUP BY d.os_name
ORDER BY avg_duration DESC
LIMIT 1

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• How would the query change if a single device could have its `os_name` updated over time? (Probes whether you'd want an SCD2 device dimension or accept current-state attribution.)
• What if we want average minutes per user per OS instead of per session? (Tests whether you can switch grain from session to user with a nested aggregation.)
• Last 30 days only, given the partition on `session_start`, what changes? (Checks that you add `WHERE us.session_start >= CURRENT_DATE - INTERVAL '30 days'` to prune partitions.)
• How do you handle sessions with NULL `device_id`? (Probes your INNER vs LEFT JOIN reasoning and whether you'd surface orphan rates to the PM.)