# Top Users by Pages Viewed

> Five users who browsed the most.

Canonical URL: <https://datadriven.io/problems/top_users_by_pages_viewed>

Domain: SQL · Difficulty: medium · Seniority: L4

## Problem

Who are the top 5 users by total pages viewed across all sessions? Show each user and their total page count, from most to fewest.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests basic aggregation with ordering and limiting. Interviewers check whether you correctly aggregate across sessions and apply the `LIMIT` for a simple top-N without tie complications.

---

### 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.

#### Step 2: Sort and limit

`ORDER BY total_pages DESC LIMIT 5` returns the top 5 users by total page count.

---

### 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 efficient. An index on `(user_id, pages_viewed)` could speed up the aggregation.

> **Interviewers Watch For**
>
> Candidates who use `MAX(pages_viewed)` instead of `SUM(pages_viewed)`. The prompt asks for total pages across all sessions, not the single highest session.

> **Common Pitfall**
>
> Using `LIMIT` without `ORDER BY` returns arbitrary rows. Always pair them together for deterministic results.

---

## 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/top_users_by_pages_viewed)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.