Forum

Posted by Dania, Oct. 17, 2022, 2:07 a.m.

Question 40

Hi, can anyone help me with what's wrong with my code?

Question:

  • Write a query to return the film_id and title of the top 2 movies that were rented the most times in June 2020
  • Use the rental_ts column from the rental for the transaction time.
  • The order of your results doesn't matter.

My code (using Postgres):

SELECT a.film_id, a.title
FROM film a
JOIN inventory b
ON a.film_id = b.film_id
JOIN (SELECT inventory_id,
      EXTRACT(YEAR FROM rental_ts) as year_,
      EXTRACT(MONTH FROM rental_ts) as mon_,
      count(rental_id)
      FROM rental
      WHERE  EXTRACT(YEAR FROM rental_ts) = 2020
      AND EXTRACT(MONTH FROM rental_ts) = 6            
      GROUP BY 1,2,3
      ORDER BY 4 DESC
      LIMIT 2) c
ON b.inventory_id = c.inventory_id

The result is different with the expected result

Answer

Answers

Comment

Hi Dania,

You're reasonably close, but there's a conceptual problem with the way you've structured this query:

The subquery is totalling the number of rentals per inventory_id and limiting it to the top two rows. The question is looking for the top two films that were rented, regardless of how many copies were rented. In other words, consider how your query would behave if there were multiple copies of a single film in inventory.

You can get there with this query structure, but you'll likely need another subquery to handle the sum of rentals and then to map the top ones to the actual title name. 

Side note, you may want to remove the year & month columns from your subquery - they're not really needed and you can still filter as you're doing in the where clause.

Hope this helps!

SQLPad user avatar

Mike (228)

Oct. 17, 2022, 1:12 p.m.