View Count Per Page

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Meta

Problem

Each row in page_views is one page view, and the logger is inconsistent about letter case, so '/Home' and '/home' are the same page recorded two ways. Show each page URL in lowercase with how many times it was viewed, most viewed first.

Every page has visitors. Some just have more.

Tables

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at TEXT

Expected output

Worked solution and explanation

What this really is

This is a row count per key, dressed up as content analytics, with two ways to get it quietly wrong. The first is reading 'how many times it was viewed' as 'how many people viewed it' and reaching for COUNT(DISTINCT user_id). In page_views only 17 users generate all the traffic, so unique viewers flatten every page to a handful and the ranking collapses. The second is grouping on the raw page_url. '/Home' and '/home' are one page logged two ways, so leaving the case alone splits each into two half-sized rows. The content team then sees two middling pages where one page is actually the busiest on the site.

Step 1: Decide what one row means

Each row in page_views is a single view event. 'How many times it was viewed' is therefore a count of rows, COUNT(*), not a count of user_id values. COUNT(user_id) is wrong too: it skips the 26 views with a NULL user, and an anonymous view is still a view.

Step 2: Normalize the key before collapsing

Group on LOWER(page_url), not page_url. The seed holds '/PRODUCTS' beside '/products' and '/Home' beside '/home'. Lowercasing in the GROUP BY merges each pair into one bucket, and returning the same LOWER(page_url) in the SELECT keeps the output key consistent with the grouping.

Step 3: Sort by the count

ORDER BY view_count DESC puts the busiest pages first. Pages with the same count may come back in any order: nothing in the question ranks one tied page above another, so no tiebreaker is owed.

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

Raw key

GROUP BY page_url returns 14 rows: '/products' at 16 and '/PRODUCTS' at 14 sit far apart, and '/home' and '/Home' tie with every minor page at 14.

Normalized key

GROUP BY LOWER(page_url) returns 12 rows: '/products' at 30 and '/home' at 28 lead clearly, which is the answer the team actually needs.

Common follow-up questions

• How would you return only the top 10 pages? (Add `LIMIT 10` after the `ORDER BY`, then decide which tied page wins if the cut lands inside a tie.)
• What if `page_url` also carries query strings or trailing slashes? (Strip them with a string or regex function before grouping, the same idea as lowercasing.)
• How would you show unique viewers alongside total views? (Add `COUNT(DISTINCT user_id)` next to `COUNT(*)` and explain why the two diverge.)