Average Session Duration by Device

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

Asked in

Amazon

Problem

Each row of user_sessions records a session's length in whole seconds and the device_id it ran on, while the type of every device is kept in devices. Report the average session length in minutes for each device type, listing the device types alphabetically.

Session length, device by device.

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

This is a unit conversion dressed up as a device comparison. The lookup is the part everyone gets right: user_sessions only knows device_id, so the type has to come from devices. What separates candidates is the minutes. session_duration_sec is an INTEGER, and integer divided by integer drops the remainder on every row, so a 53-second session counts as zero minutes. Average those truncated values and every device type comes in about half a minute short, a gap nobody notices because the numbers still look plausible.

Building it

Step 1: Attach a type to every session

Join user_sessions to devices on device_id. A plain inner join is the right call here: 23 sessions have no device_id, and a session with no device cannot be credited to a type, so letting them fall out is the answer, not an accident.

Step 2: Convert before you divide

Turn the seconds into a real number first, CAST(us.session_duration_sec AS REAL), then divide by 60. The order matters: once the value is real, the division keeps the fraction, and a 90-second session is 1.5 minutes instead of 1.

Step 3: Average per device_type, alphabetical

GROUP BY d.device_type gives one row per type, and AVG skips the 17 sessions whose duration was never recorded, which is exactly what an average of measured lengths should do. ORDER BY d.device_type delivers the alphabetical listing.

SELECT d.device_type, AVG(CAST(us.session_duration_sec AS REAL) / 60) AS avg_session_minutes FROM user_sessions AS us JOIN devices AS d ON us.device_id = d.device_id GROUP BY d.device_type ORDER BY d.device_type

Divides integers

AVG(us.session_duration_sec / 60) truncates each session first: 53 seconds becomes 0, 119 becomes 1. The average is taken over whole minutes, and every device type reads low by roughly 0.5.

Divides a real

AVG(CAST(us.session_duration_sec AS REAL) / 60) keeps 53 seconds as 0.883 minutes. The average is the true mean length, and it matches the expected output to every decimal.

Common follow-up questions

• The team now wants the median session length per device type, still in minutes. What changes? (Tests whether the candidate knows a median is not a running aggregate like `AVG` and handles even counts.)
• Sessions with no `device_id` should be reported as an 'unknown' device type instead of dropped. How do you change the query? (Tests a switch to an outer join plus a default label for the unmatched side.)
• Very long sessions (over 3,000 seconds) look like tabs left open. How would you exclude them and show how many were removed per type? (Tests a filter alongside a conditional count in the same grouped query.)