# Endless Scroll > Some visitors never close the tab. Find the five who go deepest. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem We measure engagement as the total pages a visitor loads across all of their sessions. Show the five most engaged users with their total page count, from most to fewest. ## Worked solution and explanation ### What the problem really is This is a plain roll-up wearing an engagement costume: sum pages per user, then keep the top five. LIMIT alone cannot do it, because a user owns many sessions, so you have to collapse those rows into one total per user before any ordering means anything. Everyone writes the GROUP BY. What separates people is reaching for SUM, not MAX. The prompt wants pages across every session, so MAX(pages_viewed) quietly returns each user's single busiest session and ranks the wrong five people. On the 60M-row table nobody eyeballs the output, so that swap ships looking correct. --- ### Break down the requirements #### Step 1: Aggregate pages per user `GROUP BY user_id` with `SUM(pages_viewed)` collapses 60M sessions into 4M user-level totals. This is the step LIMIT cannot skip: without the aggregation, each user still spans many rows and there is no single number to sort on. #### Step 2: Sort and limit `ORDER BY total_pages DESC LIMIT 5` returns the five users with the highest totals. The DESC ordering is what makes LIMIT deterministic; drop it and you get five arbitrary users. --- ### The solution **Aggregate and limit to top 5** ```sql SELECT user_id, SUM(pages_viewed) AS total_pages FROM user_sessions GROUP BY user_id ORDER BY total_pages DESC LIMIT 5 ``` > **Cost Analysis** > > Full scan of 60M rows for the aggregation. The `GROUP BY` reduces to 4M rows, then sorting and limiting is cheap. A covering index on `(user_id, pages_viewed)` lets the engine aggregate straight from the index and skip the heap. > **Interviewers Watch For** > > The MAX-for-SUM swap. A candidate who writes `MAX(pages_viewed)` returns each user's single peak session instead of their lifetime total, and the top five silently become the wrong people. It runs, it returns five rows, and it is wrong. > **Common Pitfall** > > Using `LIMIT` without `ORDER BY` returns arbitrary rows. Always pair them so the result is deterministic. --- ## Common follow-up questions - What if two users tie for 5th place and you need to include both? _(Tests when to switch from LIMIT to DENSE_RANK for tie inclusion.)_ - How would you add the username from a users table? _(Tests whether to join before or after aggregation for efficiency.)_ - What if pages_viewed could be NULL for some sessions? _(SUM ignores NULLs, but COALESCE might be needed if the business rule treats NULL as 0.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/endless_scroll) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.