project1_quetion1.sql

/*
  What are the top 10 most rented travel films in 2005 of over two hours duration? 
*/

SELECT DISTINCT subquery.film_id, subquery.title,
       subquery.category,
       COUNT(subquery.title) OVER(PARTITION BY subquery.title) AS rental_count
FROM (
      SELECT film.film_id film_id, film.title title,
             category.name category,
             rental.rental_date rental_date
      FROM film
      JOIN film_category
      ON film_category.film_id = film.film_id
      JOIN category
      ON film_category.category_id = category.category_id
      JOIN inventory
      ON film.film_id = inventory.film_id
      JOIN rental
      ON inventory.inventory_id = rental.inventory_id
      WHERE film.length > 120 AND
            DATE_TRUNC('year', rental.rental_date) = '2005-01-01' AND
            category.name = 'Travel'
) subquery
ORDER BY 4 DESC
LIMIT 10;