***********
- *******************************************************************************************************
- *********************************************
********************************************. - ********************************
************.
Table 1: 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
Table 2: inventory
Each row is unique, inventoy_id is the primary key of this table.
col_name | col_type --------------+-------------------------- inventory_id | integer film_id | smallint store_id | smallint
Table 3: 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
Sample results
count ------- 12345
Solution 1: postgres
SELECT COUNT(inventory_id )
FROM inventory I
INNER JOIN (
SELECT F.film_id
FROM film F
LEFT JOIN (
SELECT DISTINCT I.film_id
FROM inventory I
INNER JOIN (
SELECT inventory_id, rental_id
FROM rental
WHERE DATE(rental_ts) >= '2020-05-01'
AND DATE(rental_ts) <= '2020-05-31'
) R
ON I.inventory_id = R.inventory_id
) X ON X.film_id = F.film_id
WHERE X.film_id IS NULL
)Y
ON Y.film_id = I.film_id;
Explanation
This query is counting the number of inventory items that meet a certain condition. The inventory table is joined with a subquery that selects all film IDs that were not rented out during the month of May 2020. This is done by joining the inventory table with a rental table that filters for rentals within the specified timeframe, and then selecting distinct film IDs that were not rented out during that time. The outer query then counts the number of inventory items that correspond to these film IDs. Essentially, this query is counting the number of items in inventory that were not rented out during the month of May 2020.
Solution 2: postgres
select count(inventory_id) from inventory
where film_id not in
(
select inventory.film_id from rental
inner join inventory
on rental.inventory_id = inventory.inventory_id
where rental_ts >= '2020-05-01' and rental_ts < '2020-06-01'
)
Explanation
This query is counting the number of inventory items that were not rented out during the month of May 2020.
The first line of the query is selecting the count of inventory items. The "inventory_id" column is being counted, which means that the query will return the number of rows in the "inventory" table that meet the conditions specified in the rest of the query.
The second line of the query is filtering the results of the query. It is selecting only those inventory items where the "film_id" is not in a subquery. The subquery is selecting the "film_id" from the "inventory" table, but only for those items that were rented out during the month of May 2020.
The subquery is using an inner join to connect the "rental" and "inventory" tables using the "inventory_id" column. It is then filtering the results based on the "rental_ts" column, which specifies the date and time that the rental took place. In this case, it is selecting only those rentals that took place in May 2020.
Overall, this query is useful for determining how many inventory items were not rented out during a specific time period. This information could be helpful for identifying potential inventory management issues
Expected results