Top Users by Session Time

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

Lyft

Problem

Return the top 10 users by total session duration. Show each user's ID, username, and total duration, from highest to lowest.

They spent the most time here.

Tables

users

Columns: user_id INTEGER, username TEXT, email TEXT, signup_date TEXT, account_status TEXT, age_bucket TEXT

user_sessions

Columns: session_id INTEGER, user_id INTEGER, device_id INTEGER, session_start TEXT, session_duration_sec INTEGER, pages_viewed INTEGER

Expected output

Worked solution and explanation

What this really is

Strip the engagement framing and this is a top ten over a per-user total, not over rows. user_sessions holds one row per session, so every user fans out across several rows, and the ranking only means something after those rows collapse into one SUM(session_duration_sec) per user. Anyone can write the join. What separates candidates is whether the sort and the cut run on the summed totals or on the raw joined rows. Get it wrong and you publish the ten longest single sessions: 'alice' fills three slots with her 7200, 5400 and 3600 second sessions, and 'cyrus', the real runner-up at 6935 seconds, never appears.

Build it in order

Step 1: Join users to user_sessions on user_id

The username lives on users and the time lives on user_sessions. Keep only matched users: someone with no sessions has no total to compete with, so padding them in as zeros adds rows that can never reach the top ten.

Step 2: Collapse to one row per user

Group on u.user_id and u.username and emit SUM(s.session_duration_sec) AS total_duration. The id is the identity; username rides along only so it can be shown. SUM skips sessions whose duration was never recorded, which is what a total of recorded time should do.

Step 3: Sort the totals, then cut

ORDER BY total_duration DESC sorts the summed values and LIMIT 10 cuts after that sort, so each user can appear at most once. On this data the tenth and eleventh totals differ (5900 against 5853), so the cut lands cleanly without a tie rule.

The solution

SELECT u.user_id, u.username, SUM(s.session_duration_sec) AS total_duration
FROM users u
JOIN user_sessions s ON u.user_id = s.user_id
GROUP BY u.user_id, u.username
ORDER BY total_duration DESC
LIMIT 10

SELECT u.user_id, u.username, s.session_duration_sec AS total_duration
FROM users u
JOIN user_sessions s ON u.user_id = s.user_id
ORDER BY s.session_duration_sec DESC
LIMIT 10

Common follow-up questions

• What if you needed the total in hours instead of seconds? (Divide by a decimal, `SUM(session_duration_sec) / 3600.0`, so integer division does not truncate.)
• How would you include users with zero sessions, showing 0 for duration? (Switch to `LEFT JOIN` and wrap the sum in `COALESCE(SUM(s.session_duration_sec), 0)`.)
• Two users tie for tenth place: how do you make the cut deterministic? (Add a tie rule such as `user_id` after `total_duration` in the sort, before `LIMIT`.)
• What if some sessions never recorded a duration? (`SUM` skips `NULL` values; a user whose only session is unrecorded ends with a `NULL` total, and where that sorts depends on the engine.)