Lesson 12.5

SQL Practice: Late and Overdue Rentals

A missing returned_date means a movie is still out. Count it to the report date, or the late list skips it.

The shop rents movies for three days. A late rental is one kept longer than that, and there are two kinds: returned late, and not back yet. The second kind has no returned_date, so the value is NULL.

That NULL is the trap. Date math on it returns NULL too, and a NULL never passes a WHERE filter, so a report that subtracts dates quietly leaves out every movie still on loan: the ones the shop most needs to chase.

Give the missing date a meaning

For a movie still out, "days kept" means days up to the report date. COALESCE says exactly that: use returned_date when there is one, and the report date, July 1, 2023, when there isn't. The same expression works in the SELECT list and in the WHERE filter.

Pattern

select id_column,
       date_diff('day', start_date, coalesce(end_date, date '2023-07-01')) as days_open
from table_name

Schema · Movie Rentals Tables · rentals, customers, movies 3 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 · customers

One row per customer.

5 columns · 10 rows

customer_id int first_name text last_name text city text joined_date date

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

List every rental kept more than 3 days, whether it came back late or is still out on the report date, July 1, 2023. Return the customer's first and last name, the movie title, rental_date, returned_date, and the days kept as days_out. Sort by days_out, longest first, then by rental date.

Movie Rentals · SQL engine loading · Ctrl/⌘ + Enter to run·

Run your query to inspect the result.

Expected answer

Columns: first_name, last_name, title, rental_date, returned_date, days_out.
Rows: 7 rentals kept more than 3 days: 5 still out and 2 returned late.
Sofia Ramirez has had The Last Orchard for 26 days. Liam Nguyen's Paper Lanterns, still out after 4 days, is on the list; a rental still out after 2 days isn't.

Previous · 12.4 Who rented what

Next · 12.6 Top titles report →

Reference

SQL NULL Handling

IS NULL, COALESCE, NULLIF, and null-safe logic.

Cheatsheet

SQL NULL Handling Cheatsheet

IS NULL, COALESCE, NULLIF, and null-safe equality.

Mistake

Why = NULL Never Matches in SQL

Use IS NULL instead of equality.

Mistake