Lesson 11.3

SQL SUM Practice: Totalling Rental Revenue by Customer

Turn a stack of rentals into a per-customer revenue summary.

Each row in rentals is one transaction with an amount. To answer "who are our best customers?" you group those transactions by customer and add the money up.

COUNT and SUM often travel together: one tells you how many rentals a customer made, the other tells you how much they were worth.

Keep money readable

Summed decimals can drift into long trailing digits. Wrapping the total inround(sum(amount), 2) presents clean currency without changing the ranking. Sorting by the total descending puts the highest spenders on top, which makes a classic leaderboard.

Pattern

select group_column,
       count(*) as items,
       round(sum(amount_column), 2) as total
from table_name
group by group_column
order by total desc

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

For each customer_id, return the number of rentals (rentals) and the total amount they paid, rounded to two decimals (total_spent). Rank by total_spent descending, then customer_id.

SQL Workbench

query.sqlmovie_rentals · SQL engine loading

⌘↵ to run

Expected answer