- Write a query to return the titles of the films that were rented by our customers in
August 2020but have not been returned.
- Use
rental_tsfrom the rental table to identify when a film is rented. - If a movie is not returned, the
return_tswill beNULLin the rental table.
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
title
-------------------------
AGENT TRUMAN
ALABAMA DEVIL
AMERICAN CIRCUS
ANGELS LIFE
Expected results
Solution 1: postgres
-- FOR THOSE WHO ALREADY KNOW AND WANT TO USE OUTER JOIN
WITH out_film AS (
SELECT DISTINCT F.film_id
FROM rental R
LEFT JOIN inventory I
ON R.inventory_id = I.inventory_id
INNER JOIN film F
ON F.film_id = I.film_id
WHERE rental_ts >= '2020-08-01'
AND rental_ts <= '2020-08-31'
AND return_ts is null
GROUP BY F.film_id
)
SELECT title
FROM film F
INNER JOIN out_film OF
ON OF.film_id = F.film_id
;
Explanation
This query retrieves the titles of films that were rented out, but not returned, during the month of August 2020. It begins by creating a temporary table called "out_film" which holds the distinct film IDs of all rented films that were not returned during that time period. The query then joins this table with the "film" table to retrieve the titles of these films. The query uses an inner join to only include films that are present in both tables.
Solution 2: postgres
WITH out_film AS (
SELECT DISTINCT film_id
FROM inventory
WHERE inventory_id IN (
SELECT inventory_id
FROM rental
WHERE rental_ts >= '2020-08-01'
AND rental_ts <= '2020-08-31'
AND return_ts IS NULL
)
)
SELECT title
FROM film
WHERE film_id IN (
SELECT film_id
FROM out_film
)
;
Explanation
This query is selecting the titles of all films that were rented out and not returned between August 1st and August 31st of 2020.
The query first creates a temporary table called "out_film" that selects all distinct film IDs from the "inventory" table where the inventory was rented out during the specified time period and has not been returned yet.
The second part of the query selects the title of each film where the film ID is in the "out_film" table.
So, in summary, this query is finding all films that were rented out but not returned during August 2020 and returning their titles.