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:
My incorrect output. Some of the output in my days_between_first_and_last_rent are 1 day above my instructors output:
The rental table:
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
I was able to get the same output as my instructor when I took
and then replaced it with
I should have used a TIMESTAMPDIFF() rather than a DATEDIFF()
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.