Lesson 11.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 RentalsTable · rentals6 columns · 26 rows

Table · rentals

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

6 columns · 26 rows

rental_id intcustomer_id intmovie_id intrental_date datereturned_date dateamount decimal

Your task

Build a Top-5 most-rented report: return each movietitle, 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.

SQL Workbench

query.sqlmovie_rentals · SQL engine loading

⌘↵ to run

Expected answer