Question 40
Instruction
film_id and title of the top 2 movies that were rented the most times in June 2020rental_ts column from the rental for the transaction time.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
film_id | title ---------+-------------------- 12345 | MOVIE TITLE 1 12346 | MOVIE TITLE 2
Solution
postgresSELECT
F.film_id,
MAX(F.title) AS title
FROM rental R
INNER JOIN inventory I
ON I.inventory_id = R.inventory_id
INNER JOIN film F
ON F.film_id = I.film_id
WHERE DATE(rental_ts) >= '2020-06-01'
AND DATE(rental_ts) <= '2020-06-30'
GROUP BY F.film_id
ORDER BY COUNT(*) DESC
LIMIT 2;Explanation
This query selects the top 2 most rented films in June 2020 by joining data from the rental, inventory, and film tables. It starts by selecting the film_id and title columns from the film table and renaming the title column as "title".
Then, it joins the rental and inventory tables using the inventory_id column as the common key. It then joins the resulting table with the film table using the film_id column as the common key.
The WHERE clause filters the data to only include rentals that occurred between June 1st and June 30th, 2020.
The GROUP BY clause groups the data by film_id so that the COUNT function can be used to count the number of times each film was rented during the specified period.
Finally, the ORDER BY clause sorts the data in descending order by the count of rentals for each film. The LIMIT clause limits the output to the top 2 most rented films.
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.
10:00
Run your query to preview results here.