Тренируюсь писать запросы, нашел вот такую задачу, #32 на sqlpad.io

	Условие
	film col_name | col_type ----------------------+-------------------------- film_id | integer title | text description | text release_year | integer language_id | smallint original_language_id | smallint rental_duration | smallint rental_rate | numeric length | smallint replacement_cost | numeric rating | text inventory col_name | col_type --------------+-------------------------- inventory_id | integer film_id | smallint store_id | smallint rental col_name | col_type --------------+-------------------------- rental_id | integer rental_ts | timestamp with time zone inventory_id | integer customer_id | smallint return_ts | timestamp with time zone staff_id | smallint

Мое решение

WITH rental_inventory AS (
  SELECT 
      R.inventory_id,
      I.film_id
  FROM 
      rental R INNER JOIN inventory I ON R.inventory_id = I.inventory_id 
  WHERE
      DATE(rental_ts) >= '2020-02-01' AND DATE(rental_ts) <= '2020-02-29'
)
SELECT 
    COUNT(*)
FROM 
    film F LEFT JOIN rental_inventory RI ON F.film_id = RI.inventory_id
WHERE 
    RI.inventory_id IS null

Результат не сходится с ответом. Где тут может быть ошибка?

Тренируюсь писать запросы, нашел вот такую задачу, #32 на sqlpad.io

	Условие
	film col_name | col_type ----------------------+-------------------------- film_id | integer title | text description | text release_year | integer language_id | smallint original_language_id | smallint rental_duration | smallint rental_rate | numeric length | smallint replacement_cost | numeric rating | text inventory col_name | col_type --------------+-------------------------- inventory_id | integer film_id | smallint store_id | smallint rental col_name | col_type --------------+-------------------------- rental_id | integer rental_ts | timestamp with time zone inventory_id | integer customer_id | smallint return_ts | timestamp with time zone staff_id | smallint

Мое решение

WITH rental_inventory AS (
  SELECT 
      R.inventory_id,
      I.film_id
  FROM 
      rental R INNER JOIN inventory I ON R.inventory_id = I.inventory_id 
  WHERE
      DATE(rental_ts) >= '2020-02-01' AND DATE(rental_ts) <= '2020-02-29'
)
SELECT 
    COUNT(*)
FROM 
    film F LEFT JOIN rental_inventory RI ON F.film_id = RI.inventory_id
WHERE 
    RI.inventory_id IS null

Результат не сходится с ответом. Где тут может быть ошибка?

Вот второй вариант, здесь всё в порядке

WITH rental_feb as (
  SELECT
      inventory_id,
        rental_ts
  FROM 
    rental
  WHERE
      DATE(rental_ts) >= '2020-02-01' AND   DATE(rental_ts) <= '2020-02-29'
), films_rented as (
   SELECT 
      DISTINCT film_id 
   FROM 
      rental_feb RF INNER JOIN inventory I ON RF.inventory_id = I.inventory_id
)
SELECT 
    COUNT(*) 
FROM 
    film 
WHERE 
    film_id NOT IN (SELECT film_id FROM films_rented)