Lesson 12.6

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

Pattern

select group_column, count(*) as total
from table_name
group by group_column
order by total desc, group_column
limit 5

Schema · Website AnalyticsTable · pageviews4 columns · 75 rows

Table · pageviews

One row per page viewed within a session, in view_order.

4 columns · 75 rows

pageview_id intsession_id intpath textview_order int

Your task

Build a Top-5 most-viewed pages report: return eachpath and its number of views. Rank by views descending, break ties by path, and keep only the top five.

SQL Workbench

query.sqlwebsite_analytics · SQL engine loading

⌘↵ to run

Expected answer