Lesson 12.6

SQL Top-N Practice: A Most-Rented Movies Report

The capstone: one query that joins, groups, ranks, and trims to a Top-5 report.

Time to combine the whole chapter into one report. A "Top-N" query is a staple of real analytics: JOIN to get readable labels, GROUP BY to aggregate, ORDER BY to rank, and LIMIT to keep only the leaders.

Joining to movies lets you group by title rather than an opaque movie_id, so the finished report reads like something you would hand to a manager.

Rank with a stable tie-breaker

When two titles are rented the same number of times, the order between them is arbitrary unless you say otherwise. Ordering by times_rented first, then revenue, then title makes the Top-5 deterministic: the same five rows, in the same order, every run. That matters the moment LIMIT decides who makes the cut.

Pattern

select dim.label,
       count(*) as events,
       round(sum(fact.amount), 2) as total
from fact_table as fact
join dim_table as dim on fact.dim_id = dim.dim_id
group by dim.label
order by events desc, total desc, dim.label
limit 5

Schema · Movie Rentals Tables · rentals, movies 2 tables

Table · rentals

One row per rental. Movies still out have a null returned_date.

6 columns · 26 rows

rental_id int customer_id int movie_id int rental_date date returned_date date amount decimal

Table · movies

One row per movie in the catalog.

5 columns · 12 rows

movie_id int title text genre text release_year int rating decimal

Your task

Build a Top-5 most-rented report: return each movie title, its number of rentals (times_rented), and its total revenue rounded to two decimals. Rank by times_rented, then revenue, then title, and keep only the top five.