SQLPad Forum question: Question 44. In-demand vs not-in-demand movie…

Question 44. In-demand vs not-in-demand movies, I am getting a different answer

My Solution for getting the in-demand vs not in demand

select demand_category, count(*)
from
(select f.film_id,
case when count(rental_id) >1 then 'in-demand' else 'not-in-demand' end as demand_category
from film f
left join inventory i
on f.film_id = i.film_id
left join rental r
on r.inventory_id = i.inventory_id
where date(rental_ts) >= '2020-05-01' and date(rental_ts) <= '2020-05-31'
group by f.film_id
)x
group by demand_category

The answer for indemand matches but the not-in-demand is way off.

I took a look at the solution and Leon does a inner subquery to join rentals and inventory and left joins to the main query. I am not sure why that is different

from the way I am doing it in my solutions=

Answers

Comment

Hey Meenu,

Thanks for your question.

The issue was the where clause, the WHERE clause runs after your LEFT JOIN, so it removes all results that didn't have a match.

where date(rental_ts) >= '2020-05-01' and date(rental_ts) <= '2020-05-31'

That's why we used a subquery first before the LEFT JOIN.

Hope it helps,

Leon

Isn't that what we want? Using the where clause I am filtering out movies that were rented out in May only

The left joins before capture all the films and inventories and finally I am filtering the once rented out in the where clause

Meenu, July 3, 2023, 3:58 p.m.

Your where clause removes all films from your left join with no rentals, which should be kept.

Leon, July 3, 2023, 4:08 p.m.

Leon (949)

July 3, 2023, 3:30 p.m.