Endless Scroll

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

Yelp

Problem

Which five users loaded the most pages across all of their sessions in user_sessions, and what is each one's total page count? The growth team's engagement board lists them most pages first, with the lower user_id taking the place when two totals are level.

Some visitors never close the tab. Find the five who go deepest.

Tables

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

This is a per-user roll-up with a contested last seat, dressed up as an engagement leaderboard. Everyone sees that a user owns many rows in user_sessions and writes the GROUP BY user_id. Two things separate people. First, the metric is a total: SUM(pages_viewed), not MAX(pages_viewed) and not COUNT(*), and the max quietly ranks each user's single busiest session. Second, the fifth seat is a six-way tie: six users finish on exactly 135 pages, so LIMIT 5 after sorting on the total alone hands the seat to whichever row the engine happens to emit first. The statement names the rule (the lower user_id wins), and that rule has to live in the ORDER BY before the cut, or the answer changes between runs.

Build it in three moves

Step 1: Collapse sessions to one total per user

A user owns between one and eight rows in user_sessions, so no single row is their engagement. GROUP BY user_id with SUM(pages_viewed) AS total_pages produces the one number per user that the rest of the query sorts on. Sorting raw rows instead gives single-session counts that top out below 50 and can list the same user twice.

Step 2: Sort by the total, most pages first

ORDER BY total_pages DESC puts users 391 and 1361 (145 each) on top, then 682 and 1652 (140). Sorting by the alias is fine: ORDER BY is evaluated after the SELECT names it.

Step 3: Break the tie before you cut

Six users sit on 135, user 100 among them. LIMIT 5 keeps one of them, and only a second sort key decides which. Adding user_id ascending makes user 100 the fifth row every time, which is exactly what the tie rule in the statement asks for.

The solution

SELECT user_id, SUM(pages_viewed) AS total_pages
FROM user_sessions
GROUP BY user_id
ORDER BY total_pages DESC, user_id
LIMIT 5

Sort on the total only

ORDER BY total_pages DESC LIMIT 5 returns four fixed rows and then any one of six users at 135. Two correct-looking runs can disagree on the fifth row.

Total, then user id

ORDER BY total_pages DESC, user_id LIMIT 5 pins the fifth row to user 100, the lowest id at 135, so every engine and every run returns the same five.

Common follow-up questions

• What if every user tied at the fifth total should be included, not just one? (Tests moving from a row cut to a rank-based cut that keeps ties: `RANK()` over `total_pages DESC`, filtered to 5 or below.)
• How would you add each user's `username` from `users`? (Tests aggregating before the join so the per-user result, not the session table, meets `users`.)
• What if `pages_viewed` could be NULL for some sessions? (`SUM` skips NULLs; `COALESCE(pages_viewed, 0)` only matters if a user could have no non-NULL session at all.)