Dave's MySQL Quiz #2

This week's MySQL uses the Sakila database (See below for details on obtaining and installing this sample database)  and this week's quiz is a  great one for those wanting to move from beginner level SQL development to a higher level.  There will be lots of tables to joins.  And depending on how you decided to the solve this problem you may also end up using window functions and groupings.

    The problem: 

Find the customers with more than one unreturned rented movies that are past their return due date. 

You might to look at this for the example of finding an overdue rentals.  It shows you a similar query on which you could base your solution and it does  show you how the various tables can be joined together to get the desired solution.     

You will need to display the customer's ID number, the number of overdue videos, and the names of the videos! 

Bonus points for the customer name!

 

The Sakila Example Database:

See https://dev.mysql.com/doc/sakila/en/ for information on obtaining, installing, and using this example database. 

Once upon a time before streaming services were popular, if you wanted to see a video movie in your home you had to first go to a store filled with BETAMAX and VHS format tapes and rent them.  The Sakila database is data representing the operations of such a store.  This is a bigger set of data than the previous two and provides twenty three tables giving novices an opportunity to join more tables together.  

  And Sakila is the name of the MySQL Dolphin Mascot 

A Solution:

Please note that your solution may be different than this which hopefully shows the amazing flexibility of Structured Query Language. 

 

First we need to get the customer_id from the customer table.  Then it takes a bit of struggle to get the information on the rental.   It is often easier to write queries by determining the needed output columns, then the 'qualifiers'  or stuff on the right of the WHERE clause before determining what has to be joined to get between the two.

 

The part of the query to find the overdue entries requires the rental date where it and the length of rental time are before the current date.  r.rental_date + INTERVAL f.rental_duration DAY < CURRENT_DATE()

 

Finding the those customers with more than one rental needs to have the count of r.rental_id greater than 1. 

 

So besides the rental table, we will need the inventory table to tie between the rental table and the film table. 

 SQL > select c.customer_id,    

sum(count(r.rental_id)) over (partition by c.customer_id) as'total',  

r.rental_id, group_concat(f.title order by f.title) as 'titles' 

from rental r   

join inventory i on (r.inventory_id=i.inventory_id)   

join film f on i.film_id = f.film_id   

join customer c on r.customer_id = c.customer_id   

where r.return_date IS NULL   

AND r.rental_date + INTERVAL f.rental_duration DAY < CURRENT_DATE() 

group by c.customer_id  

having count(r.rental_id) >  1  

order by c.customer_id;

+-------------+-------+-----------+----------------------------------------+
| customer_id | total | rental_id | titles                                 |
+-------------+-------+-----------+----------------------------------------+
|          15 |     2 |     13798 | CANDIDATE PERDITION,SMOKING BARBARELLA |
|          42 |     2 |     13351 | RIVER OUTLAW,TORQUE BOUND              |
|          43 |     2 |     15644 | MOTIONS DETAILS,POLLOCK DELIVERANCE    |
|          53 |     2 |     11657 | LAWLESS VISION,PEACH INNOCENT          |
|          60 |     2 |     12489 | BOOGIE AMELIE,CHAMBER ITALIAN          |
|          75 |     3 |     13534 | LUST LOCK,SLEEPY JAPANESE,TROUBLE DATE |
|         107 |     2 |     13079 | BLADE POLISH,CLUB GRAFFITI             |
|         155 |     2 |     11496 | CHASING FIGHT,HYDE DOCTOR              |
|         163 |     2 |     11754 | HOLES BRANNIGAN,SONS INTERVIEW         |
|         175 |     2 |     13161 | DEER VIRGINIAN,PIRATES ROXANNE         |
|         208 |     2 |     13719 | CURTAIN VIDEOTAPE,SEATTLE EXPECATIONS  |
|         216 |     2 |     11676 | SWEDEN SHINING,WOMEN DORADO            |
|         228 |     2 |     12672 | CYCLONE FAMILY,GRAPES FURY             |
|         267 |     2 |     12066 | LUST LOCK,PHILADELPHIA WIFE            |
|         269 |     2 |     12610 | PRINCESS GIANT,THEORY MERMAID          |
|         284 |     2 |     12064 | BERETS AGENT,FRIDA SLIPPER             |
|         354 |     2 |     11782 | TITANIC BOONDOCK,TROJAN TOMORROW       |
|         361 |     2 |     13298 | HALF OUTFIELD,INSECTS STONE            |
|         448 |     2 |     13577 | FAMILY SWEET,STATE WASTELAND           |
|         457 |     2 |     12645 | CLEOPATRA DEVIL,GLEAMING JAWBREAKER    |
|         516 |     2 |     12130 | FALCON VOLUME,MINORITY KISS            |
|         560 |     2 |     12116 | MOVIE SHAKESPEARE,PIANIST OUTFIELD     |
|         576 |     2 |     11942 | TITANIC BOONDOCK,VANISHED GARDEN       |
+-------------+-------+-----------+----------------------------------------

Bonus:  Add the customer name

 

select c.customer_id, 

 concat(c.first_name, ' ', c.last_name) AS 'Customer Name',

  sum(count(r.rental_id)) over (partition by c.customer_id) as 'tots',  

 r.rental_id, group_concat(f.title) from rental r 

 join inventory i on (r.inventory_id=i.inventory_id) 

 join film f on i.film_id = f.film_id 

 join customer c on r.customer_id = c.customer_id 

 where r.return_date IS NULL 

 AND r.rental_date + INTERVAL f.rental_duration DAY < CURRENT_DATE() 

 group by c.customer_id

 having count(r.rental_id) >  1

 order by c.customer_id;