Posted by Sylvia, April 4, 2022, 6:09 p.m.
Question #40 Top 2 Movies
My solution is giving me three top 2 movies because there is a tie for being the 2nd most popular movie.
My code is listed as below:
WITH rent_time AS
(
SELECT a.film_id,
count(b.rental_id) as num
FROM inventory a
INNER JOIN rental b
on a.inventory_id = b.inventory_id
WHERE EXTRACT(MONTH FROM b.rental_ts) = '6' AND EXTRACT(YEAR FROM b.rental_ts) = '2020'
GROUP BY a.film_id
),
cte AS
(SELECT *, RANK() over (ORDER BY num DESC) as rank
FROM rent_time
)
SELECT a.film_id, a.title
FROM film a
INNER JOIN cte b
on a.film_id = b.film_id
WHERE b.rank IN (1, 2)
In the case of having a TOP 1 but 2 TOP 2 movies, should I use RANK() OVER function or LIMIT? If it were in a real interview, should I ask the interviewer to clarify how to take care of the ties, such as "Would you like to see 3 results for the TOP 1 movie when there is a tie"?