CODEWITHSUNDEEP
mysql
Kyle
Kyle

Reputation: 154

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

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

Upvotes: 0

Views: 1633

Answers (2)

Kyle
Kyle

Reputation: 154

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.

Upvotes: 0

Reversal
Reversal

Reputation: 622

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)

Upvotes: 1

Related Questions