31. Movies that have not been returned

easy

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



More IN, BETWEEN, LIKE, CASE WHEN questions

ID Title Level FTPR
178 Members who worked at both Microsoft and Google linkedin medium
34%
177 Purchases by platform report amazon medium
10%
176 Employees' annual bonus amazon easy
12%
160 Sellers with no sales by day ebay hard
11%
156 Cancellation rate by unbanned users lyft hard
15%
155 Driver with the highest cancellation rate lyft easy
24%
151 Salary report dropbox easy
18%
138 Happy restaurants doordash easy
14%
136 Extremely late orders doordash easy
11%
131 Churned accounts affirm hard
10%
102 Histogram by visit session duration mobile hard
17%
35 Film length report easy
27%
34 Stocked up movies easy
27%
33 Returning customers medium
8%
32 Unpopular movies hard
17%
30 Inactive customers in May easy
22%
29 Second highest spend customer medium
14%
28 Film with the second largest cast medium
26%
27 Film with the largest cast easy
27%
26 Second shortest film easy
30%
25 Shortest film easy
46%
24 Films with more than 10 actors medium
19%
22 Average cost per rental transaction easy
42%
15 Fast movie watchers vs slow watchers hard
6%
14 Good days and bad days hard
4%
13 Actors' first name medium
14%
12 Actors' last name ending in 'EN' or 'RY' easy
27%
11 Actors' last name easy
21%