Posted by Anastasiia, March 28, 2022, 2:26 a.m.

45. Movie inventory optimization

Hi!

I've just done this task and have a doubts about dectection movies that were in demand in May 2020. At first I think about four cases:

Rental date in may 2020:

date_trunc('month',rental_ts)='2020-05-01 00:00:00'

Return date in may 2020:

date_trunc('month',return_ts)='2020-05-01 00:00:00'

Rental date before May 2020 and Return date after May 2020:

(rental_ts<'2020-05-01 00:00:00' and return_ts >='2020-06-01 00:00:00')

Rental date before May 2020 and return date is null (still in use)
```
(rental_ts<'2020-05-01 00:00:00' and return_ts is null)
```

But debugging queris shows that you don't count last variant. Why is it?

My full query for detecting movies that were in demand in May 2020:

SELECT i.film_id, i.inventory_id, r.rental_ts, r.return_ts
FROM film f
	inner join inventory i
		on i.film_id = f.film_id
	inner join rental r
		on i.inventory_id = r.inventory_id
		and (date_trunc('month',rental_ts)='2020-05-01 00:00:00'
				or date_trunc('month',return_ts)='2020-05-01 00:00:00'
				or (rental_ts<'2020-05-01 00:00:00' and return_ts is null)
				or (rental_ts<'2020-05-01 00:00:00' 
					and return_ts >='2020-06-01 00:00:00')
			)

This question is closed

Answers

Comment

So first off, I'd say nice work on considering the scenarios here - I certainly didn't think about this one when I originally did this problem.

For reasoning, I could see this going either way - does a rental made outside the window of interest count as 'in-demand'? In one way, I'd say sure as they obviously wanted it long enough to keep paying rental fees / etc. That said, let's consider a few other options:

- Most systems would have something in place that after a certain timeframe, an item is either re-rented or purchased automatically. Given that all the entries that meet this scenario (rented before May 1, but not returned) are of the date Feb 14, 2021, these would likely fall into that category. The data model may not fit the scenario perfectly, but this often happens during analysis and is likely better to ignore it (especially as there are 182 instances)

- Let's consider a different scenario where we weren't looking at in-demand rentals, but rather purchases. Would you count sales that fall outside a timeframe of interest as in-demand? If you had a different way to log interest, perhaps, but most likely it wouldn't count toward any metrics you calculated.

- These scenarios are designed to illustrate the types of problems you'd run into with data analysis / science / engineering, but are definitely places bugs may exist as well. I believe in this case that since there are 182 of these entries that all share the same date, this is simply a scenario that wasn't considered.

Overall, I'd treat this as extraneous data outside the question, but again, excellent job on considering the scenarios!

Mike (228)

March 28, 2022, 8:23 a.m.

Comment

The return date has nothing to do with the purpose of this question, as we only care about demand.

Leon (949)

March 28, 2022, 11:19 a.m.