# View Count Per Page

> Every page has visitors. Some just have more.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The content team needs a per-page view count to prioritize optimization efforts. Show each page URL and how many times it was viewed, sorted from most viewed to least.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a basic GROUP BY with COUNT and ORDER BY. Interviewers use it to verify that you can aggregate event data and sort the result for prioritization.

---

### Break down the requirements

#### Step 1: Group by page URL

`GROUP BY page_url` partitions the 400M page views into groups by URL.

#### Step 2: Count views per page

`COUNT(*)` returns the view count for each page.

#### Step 3: Sort by view count descending

`ORDER BY view_count DESC` surfaces the most viewed pages first.

---

### The solution

**Group, count, and sort for page popularity**

```sql
SELECT page_url, COUNT(*) AS view_count
FROM page_views
GROUP BY page_url
ORDER BY view_count DESC
```

> **Cost Analysis**
>
> Full scan of 400M rows. The GROUP BY reduces to 35K distinct page URLs. The sort on 35K rows is trivial. The bottleneck is the initial full scan.

> **Interviewers Watch For**
>
> Clean, minimal query structure. No unnecessary JOINs, subqueries, or columns.

> **Common Pitfall**
>
> Forgetting `ORDER BY` when the prompt asks for results sorted by view count. Without it, the output order is undefined.

---

## Common follow-up questions

- How would you show only the top 10 pages? _(Add LIMIT 10 after the ORDER BY.)_
- What if page_url has query parameters you want to ignore? _(Strip parameters with SUBSTR or a URL parsing function before grouping.)_
- How would you add unique visitor count alongside total views? _(Add COUNT(DISTINCT user_id) to the SELECT.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/view_count_per_page)
- [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.