Q45

Hi,

At the first glance I saw this question, I thought I can leverage left-join pair up with Nullif/Coalesce to tackle this. Yet none of them work as intended. Did I miss something here? Cuz the inventory table does have some record(in this scenario, film_id) that was not shown in the rental table, 'Null' will pop up, and coalesce will convert those nulls to zero. Yet after I output the film_rent_cnt table, it doesn't yield any record with 0 "rent_cnt"

WITH film_rent_cnt AS
(SELECT i.film_id, COALESCE(COUNT(r.rental_id),0) as rent_cnt
FROM inventory i 
LEFT JOIN rental r
ON i.inventory_id = r.inventory_id
WHERE DATE(rental_ts) >= '2020-05-01'
      AND DATE(rental_ts) <= '2020-05-31'
GROUP BY 1
)

SELECT COUNT(DISTINCT inventory_id) as count
FROM inventory
WHERE film_id IN (SELECT film_id
                  FROM film_rent_cnt
                  WHERE rent_cnt = 0