SQL Top-N Practice: A Most-Viewed Pages Report
The closing capstone: group pageviews by path, rank them, and keep the top five.
Every row in
pageviews
is one page load, tagged with the
path
that was viewed. "What are our most popular pages?" is a Top-N report. The
same pattern you used for the most-rented movies, on a new table.
Group by
path, count the views, rank them, and keep only the leaders with
LIMIT.
The tie-breaker earns its keep
Several paths are viewed the same number of times, so where the
LIMIT
cut lands depends on the sort. Ordering by
views
and then
path
makes the Top-5 stable and repeatable, a fitting way to close out the
chapter.
select group_column, count(*) as total
from table_name
group by group_column
order by total desc, group_column
limit 5Schema · Website Analytics Table · pageviews 4 columns · 75 rows
One row per page viewed within a session, in view_order.
Build a Top-5 most-viewed pages report: return each path and its number of views. Rank by views descending, break ties by path, and keep only the top five.
Run your query to inspect the result.
Expected answer
- Columns: path, views.
- Rows: 5, the most-viewed paths.
- The home page (/) leads with 26 views.
That's the last lesson in “Applied Practice: Website Analytics.”
The next chapter builds on what this one covered.
Start Chapter 14: Applied Practice: Store Operations → Begins with 14.1 Daily revenue reportRelated
Nested SELECTs, IN, EXISTS, and correlation.
Ranking, LAG/LEAD, and running totals at a glance.
Practice ranking, running totals, and top row per group.
The default RANGE frame groups tied rows; use ROWS.
Filter window results in a CTE or with QUALIFY.
The WITH clause, chained steps, and CTE vs subquery.