- Write a query to return the titles of the films that were rented by our customers in
August 2020
but have not been returned.
- Use
rental_ts
from the rental table to identify when a film is rented. - If a movie is not returned, the
return_ts
will beNULL
in 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.