Question 31
August 2020 but have not been returned.rental_ts from the rental table to identify when a film is rented. 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
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
postgresWITH 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.
Last Submission
postgresNo submission yet for this engine. Run and submit your query to save it here.
Submit a query to compare against expected output.
Interview timer
Recommended interview pacing
Easy: 5 min for direct warm-up style questions.
Medium: 10 min for multi-step interview queries.
Hard: 15 min for layered questions with tighter time pressure.
A common bar is solving about 2 medium-or-harder questions in a 30 minute interview.
5:00
Run your query to preview results here.