<file_sep>-- 1. Write a query to display for each store its store ID, city, and country.
select s.store_id, c.city, co.country
from sakila.store s
inner join sakila.address a
using (address_id)
inner join sakila.city c
using (city_id)
inner join sakila.country co
using (country_id);

-- 2. Write a query to display how much business, in dollars, each store brought in.
select s.store_id, sum(p.amount)
from sakila.store s
inner join sakila.staff staff
using (store_id)
inner join sakila.payment p
using (staff_id)
group by s.store_id;
-- 3. What is the average running time of films by category?
select name, avg(length) as runTime
from sakila.category
inner join sakila.film_category fc
using (category_id)
join sakila.film f
using (film_id)
group by name
order by runTime desc;
-- 4. Which film categories are longest?
-- 			how do you define the longest? im gunna say top 3.
select name, avg(length) as runTime
from sakila.category
inner join sakila.film_category fc
using (category_id)
join sakila.film f
using (film_id)
group by name
order by runTime desc
limit 3;
-- 5. Display the most frequently rented movies in descending order.
select title, count(rental_id) as timesRented
from sakila.film
join sakila.inventory
using (film_id)
join sakila.rental
using (inventory_id)
group by title
order by timesRented desc;

-- 6. List the top five genres in gross revenue in descending order.
select name, sum(amount) as grossRevenue
from sakila.category
inner join sakila.film_category 
using (category_id)
join sakila.inventory
using (film_id)
join sakila.rental
using (inventory_id)
join sakila.payment
using (rental_id)
group by name
order by grossRevenue desc
limit 5;
-- 7. Is "Academy Dinosaur" available for rent from Store 1?
select * from(
select i.inventory_id, f.film_id, f.title, i.store_id, r.rental_date, r.return_date
, rank() over(partition by i.inventory_id order by r.rental_date desc) as myRank
from sakila.film f
join sakila.inventory i
using (film_id)
join sakila.rental r
using (inventory_id)
where f.title = "Academy Dinosaur" and i.store_id = 1)sub
where myrank =1 and return_date is not null;

