"longest film rental duration and the customer who rented the said film" MySQL Sakila query

Question

I'm trying write a query that would return the longest film rental duration and the customer who rented the said film (and also the title of the film) in the Sakila database.

As a first step, I've wrote the following query, which is supposed to return the film_id, inventory_id, customer_id and the longest duration of rental (in weeks):

SELECT DDIFF.*

FROM (SELECT rental.inventory_id, rental.customer_id, DATEDIFF(rental.return_date, rental.rental_date)/7 AS TM FROM rental) AS DDIFF

INNER JOIN  (SELECT  DDIFF.inventory_id, DDIFF.customer_id, MAX(DDIFF.TM) AS WEEKS 
FROM (SELECT  rental.inventory_id, rental.customer_id, DATEDIFF(rental.return_date, rental.rental_date)/7 AS TM FROM rental) AS DDIFF
GROUP BY DDIFF.inventory_id, DDIFF.customer_id) AS MXLST 

ON  DDIFF.TM = MXLST.WEEKS AND  MXLST.inventory_id = DDIFF.inventory_id AND MXLST.customer_id = DDIFF.customer_id

;

yet I'm getting some unexpected results (it should return multiple rows with the longest duration being 1.4286 weeks, but I'm getting 1.0 weeks for some reason). What am I doing wrong? I'd be very thankful to hear an explanation.

Note that I've tried following this and this to properly use MAX() yet it still doesn't return the correct results.

Edit: Sakila tables` structure can be found here

Your SQL is more advanced than my minds eye, so I am speculating the cause as 1) the result is rounded to the nearest integer or 2) the result is written to a column/variable that expects integers only. — , Apr 02 '16 at 10:49
@fiprojects I don't think that's the case, since I found names people who were in the results list which weren't supposed to be there. Thanks for your input nevertheless. — Kyle, Apr 02 '16 at 11:19

Reversal · Accepted Answer · 2016-04-02T11:43:16.690

1

Your query is highly inefficient: you are performing 3 SELECTs on full tables and then other operations. I tested your query: it's been running for more than 60 seconds without giving any result. Just killed it and rewrote your query as follows:

SELECT c.first_name, c.last_name, f.title, DATEDIFF(r.return_date,r.rental_date)/7 AS rental_time 
FROM rental AS r
INNER JOIN inventory AS i 
ON r.inventory_id = i.inventory_id
INNER JOIN film AS f
ON i.film_id = f.film_id
INNER JOIN customer AS c
ON r.customer_id = c.customer_id
HAVING rental_time = (SELECT MAX(DATEDIFF(return_date,rental_date))/7 FROM rental)

edited Apr 02 '16 at 11:43

answered Apr 02 '16 at 11:01

Reversal

622
5
19

Maybe I haven't interpreted your question correctly. You want to calculate what's the maximum rental time in weeks and then select every rental with such a time? – Reversal Apr 02 '16 at 11:21
I wanna return the full name of the people who rented a film for the longest amount of time in weeks, the title of the film they rented, and the amount of time they rented it for (in weeks - which happens to be 1.4286 weeks for all these people). – Kyle Apr 02 '16 at 11:30
Update: figured it out myself and posted an answer. Thank you for trying at least :) – Kyle Apr 02 '16 at 11:39
Very nice answer! Much more readable than what I posted! Upvoted and selected it as best answer, thank you! – Kyle Apr 02 '16 at 11:46

score 0 · Answer 2 · edited Apr 02 '16 at 13:06

Seems like I've managed to solve my own question. Here's an updated query for future reference:

SELECT customer.first_name,
       customer.last_name,
       film.title,
       DDIFF.TM AS 'rental duration'
FROM customer,
     film,
     (SELECT inventory.film_id,
             rental.inventory_id,
             rental.customer_id,
             DATEDIFF(rental.return_date, rental.rental_date)/7 AS TM
      FROM rental,
           inventory
      WHERE rental.inventory_id = inventory.inventory_id) AS DDIFF

INNER JOIN (SELECT DDIFF.film_id,
              DDIFF.inventory_id,
              DDIFF.customer_id,
              MAX(DDIFF.TM) AS WEEKS 
            FROM (SELECT inventory.film_id,
                         rental.inventory_id,
                         rental.customer_id,
                         DATEDIFF(rental.return_date, rental.rental_date)/7 AS TM
                  FROM rental,
                       inventory
                  WHERE rental.inventory_id = inventory.inventory_id) AS DDIFF
            GROUP BY DDIFF.film_id,
                     DDIFF.inventory_id,
                     DDIFF.customer_id) AS MXLST 
ON  DDIFF.TM = (SELECT MAX(DDIFF.TM)
                FROM (SELECT inventory.film_id,
                             rental.inventory_id,
                             rental.customer_id,
                             DATEDIFF(rental.return_date, rental.rental_date)/7 AS TM
                      FROM rental,
                           inventory
                      WHERE rental.inventory_id = inventory.inventory_id) AS DDIFF)
AND MXLST.film_id = DDIFF.film_id
AND MXLST.inventory_id = DDIFF.inventory_id
AND MXLST.customer_id = DDIFF.customer_id

WHERE customer.customer_id = DDIFF.customer_id
AND film.film_id = DDIFF.film_id;

I admit, it's not efficient nor looks decent, but it's a correct answer. Anyone is welcome to try and find a much more efficient/readable query that returns the same result. Anyway, thanks to everyone who tried to help.

"longest film rental duration and the customer who rented the said film" MySQL Sakila query

2 Answers2