Posted by Luis, Dec. 26, 2022, 7:32 p.m.

#15 Fast movie watchers vs slow movie watchers

Hey,

I'm struggling to understand why the given solution is correct. If I break it down, the number of rounded up days for each rental is given by:

ROUND(DATEDIFF(return_ts, rental_ts) + 1)

I checked some random rentals for customer 130 using the following:

SELECT
customer_id,
rental_ts,
return_ts,
ROUND(DATEDIFF(return_ts, rental_ts) + 1)
FROM rental
WHERE return_ts IS NOT NULL AND customer_id = 130

It yielded:

customer_id	rental_ts	return_ts	ROUND(DATEDIFF(return_ts, rental_ts) + 1)
130	2020-05-24T22:53:30	2020-05-26T22:04:30	3
130	2020-05-29T09:25:10	2020-06-02T04:20:10	5
130	2020-06-16T07:55:01	2020-06-19T06:38:01	4

In the first case the return time is just under 2 days, but the solution seems to be arbitrarily adding 1 to make it 3 days? This may work using FLOOR(), but ROUND() rounds either up or down depending on the value. The solution then takes the AVG of these days:

AVG(ROUND(DATEDIFF(return_ts, rental_ts ) + 1))

My solution instead finds the rental period using the following:

CEILING(TIMESTAMPDIFF(HOUR, rental_ts, return_ts)/24)

customer_id	rental_ts	return_ts	CEILING(TIMESTAMPDIFF(HOUR, rental_ts, return_ts)/24)
130	2020-05-24T22:53:30	2020-05-26T22:04:30	2
130	2020-05-29T09:25:10	2020-06-02T04:20:10	4
130	2020-06-16T07:55:01	2020-06-19T06:38:01	3

I may be missing something since I'm pretty new to SQL (that's why I'm here), let me know if I may be making a mistake.

Answer

Answers

Comment

Hey Luis,

Glad to see you are breaking down the queries step by step and was able to look at the intermediary results. It's a great method to learn SQL.

To answer your question, very often there is no 'scientific' definition of what a metrics should be, so the ROUNDED UP formula is one way to do it, (not perfect),

The formula is something that I think is reasonable way to look at it, of course there are many different ways to come up with your own definition.

ROUND(DATEDIFF(return_ts, rental_ts) + 1)

Hope it makes sense. Let us know if you have more questions.

Leon

Leon (949)

Jan. 3, 2023, 10:01 a.m.

Comment

@Luis - I did the same calculation you did using AVG(CEILING(TIMESTAMPDIFF(HOUR, rental_ts, returned_ts) / 24)). Note I am working in mySQL.

I can appreciated @Leon's response that exactly how metrics are defined can be hazy and vary from business to business. That said, if I was a customer who paid for a one day rental at 18:00 on a Friday night, and returned it at 11:00 on Saturday morning, I'd be pretty choked to be told I was getting a late charge of one day!

I also have to admit, I'm a little unsure what the ROUND() in @Leon's solution is doing. DATEDIFF only uses the DATE portions of the DATE-TIME and returns a whole integer as per the docs.

@Leon maybe you could expand on the purpose of ROUND() here?

Hey Will,

That's a great question, the ROUND logic is kinda arbitrary, yeah in the old days, if you miss the return window by just one hour, Blockbuster (for physical movie/game rental, which is no longer in business, lol) will still charge you one more day of rental fee.

That's kinda the my reasoning behind the +1 ROUND logic, however, I don't like this rule and I do agree it's up for debate.

If you have a better proposal, please let me know!

Thanks,

-Leon

Leon, Jan. 21, 2023, 12:35 p.m.

Hi Leon - no suggestion other than I am partial to Luis' approach. Thanks!

Will, Jan. 24, 2023, 10:32 a.m.

Makes sense @will, will come back and keep revisiting this problem.

Leon, Jan. 29, 2023, 10:11 a.m.

Will (24)

Jan. 9, 2023, 2:21 p.m.