Script-Dia4.sql

--Consulta todos los clientes que han alquilado pelculas 
select distinct c.customer_id, c.first_name, c.last_name 
from customer c 
right join rental r on r.customer_id = c.customer_id 
order by c.customer_id desc;

--Obtenga todos los clientes que hayan vencido la fecha de vencimiento de pago.Muestra tambin por qu pelculas vencen sus pagos.
select distinct f.film_id, c.customer_id, c.first_name, f.title from customer c
right join rental r on r.customer_id = r.customer_id
right join inventory i on i.inventory_id = r.inventory_id 
right join film f on f.film_id = i.film_id 
where r.return_date < current_date ;

--Cules son las categoras ms alquiladas?
select c.name, count(c.category_id) from category c 
join film_category fc ON fc.category_id = c.category_id 
join film f on f.film_id = fc.film_id 
join inventory i on i.film_id = f.film_id 
join rental r on r.inventory_id = i.inventory_id 
group by(c.category_id)
order by count(c.category_id) desc;

--Cuales son las categoras ms alquiladas agrupadas por cliente.
select c2.customer_id, c2.first_name, c.name, count(c.category_id) from category c 
join film_category fc ON fc.category_id = c.category_id 
join film f on f.film_id = fc.film_id 
join inventory i on i.film_id = f.film_id 
join rental r on r.inventory_id = i.inventory_id 
join customer c2 on r.customer_id = c2.customer_id 
group by(c2.customer_id , c.category_id)
order by customer_id, count(c.category_id) desc;

--Cual es el lenguaje ms alquilado
select l.language_id, l.name, count(rental_id) from language l 
join film f ON f.language_id = l.language_id 
join inventory i on i.film_id = f.film_id 
join rental r on r.inventory_id = i.inventory_id 
group by (l.language_id)
order by count(r.rental_id) desc
limit 1;

--Cul empleados son los mejores. Significa cual empleados se alquila maximo pelculas.
select s.staff_id, s.first_name, count(r.rental_id) from staff s 
join rental r on r.staff_id = s.staff_id 
group by(s.staff_id)
order by count(r.rental_id) desc;

--Quin es el actor ms famoso. Significa pelculas de cual actor se alquila maximo.
select a.actor_id, a.first_name, a.last_name, count(r.rental_id) from actor a 
join film_actor fa on fa.actor_id = a.actor_id 
join film f on f.film_id = fa.film_id 
join inventory i on i.film_id = f.film_id 
join rental r on r.inventory_id = i.inventory_id 
group by(a.actor_id)
order by count(r.rental_id) desc
limit 1;

--Quien es el mejor cliente. Significa quien alquila mximo?
select c.customer_id, c.first_name, c.last_name, count(r.rental_id) from customer c
join rental r on r.customer_id = c.customer_id 
group by(c.customer_id)
order by count(r.rental_id) desc
limit 1;