skip to Main Content

I’m having some trouble with the final column that is to give me the number of days between the first and last date a movie was rented.

My instructors correct output:

enter image description here

My incorrect output. Some of the output in my days_between_first_and_last_rent are 1 day above my instructors output:

enter image description here

The rental table:

enter image description here

The DATEDIFF is right above the FROM clause at the bottom


-- NOW()'Jordan_Rasmussen' just shows my name in the output for my teacher
-- Get a list of film titles
SELECT NOW()'Jordan_Rasmussen',
f.title,

( -- Get the count of the inventory 
    SELECT COUNT(i2.inventory_id)
    FROM inventory AS i2 
    WHERE i2.film_id = f.film_id 
    ) AS inventory_count,

-- Get the count of the number of times a movie was rented 
COUNT(r.rental_id) AS num_times_rented,

-- Determine the demand of the film by the number of times it was rented
-- If the film has no inventory, then its demand is 'no inventory' 
CASE
WHEN COUNT(i.inventory_id) = 0  THEN 'No Inventory'
WHEN COUNT(r.rental_id) > 20 THEN 'Fire'
WHEN COUNT(r.rental_id) > 10 THEN 'Hot'
WHEN COUNT(r.rental_id) > 5 THEN 'Warm'
ELSE 'Flop'
END AS demand,

-- Get the date of the first time the movie was rented
MIN(DATE(r.rental_date)) AS first_date_rented,

-- Get the number of days between the first and last day the movie was rented
DATEDIFF(MAX(DATE(r.rental_date)),MIN(DATE(r.rental_date))) AS days_between_first_and_last_rent

FROM film AS f 
LEFT JOIN inventory AS i ON f.film_id = i.film_id 
LEFT JOIN rental AS r ON i.inventory_id = r.inventory_id

GROUP BY f.title
ORDER BY f.title 

I’m just not sure why I’m getting +1 for some rows, but not all of them.

2

Answers


  1. Chosen as BEST ANSWER

    I was able to get the same output as my instructor when I took

    DATEDIFF(MAX(DATE(r.rental_date)),MIN(DATE(r.rental_date))) AS days_between_first_and_last_rent
    

    and then replaced it with

    TIMESTAMPDIFF(DAY,MIN(r.rental_id),MAX(r.rental_id) AS days_between_first_and_last_rent. 
    

    I should have used a TIMESTAMPDIFF() rather than a DATEDIFF()


  2. Your query looks fine. I suppose that your teacher just didn’t reduce the datetimes to dates, so a renting period from '2020-01-15 10:00:00' until '2020-01-16 09:00:00' is one day less than until '2020-01-16 11:00:00' for them.

    That would explain the one-day differences you are seeing.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search