Soluciones varias

-- Clientes de España o Argentina

select customer.* from country join city on country.country_id=city.country_id
join address on city.city_id=address.city_id
join customer on address.address_id=customer.address_id
where country in ('spain','argentina');

-- Ventas totales por empleado

select staff.*, sum(amount) total from staff 
join payment on staff.staff_id=payment.staff_id
group by staff.staff_id;

-- Películas en las que han trabajado más de 10 actores

select film.*, count(actor_id) actores from film 
join film_actor on film.film_id=film_actor.film_id
group by film_id
having actores>10;

-- El título de la película que más se ha alquilado (en número de alquileres)

-- Solución decente pero no correcta
-- ¿Por qué? Porque si hay varias películas que tengan el mismo número
-- de alquileres solo saldrá una
select title, count(rental_id) alquileres from film 
join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
group by film.film_id
order by alquileres desc
limit 1;

-- ¿Cual sería la solución correcta?
-- Buscar el máximo de alquileres
-- Selecciona las películas que tengan ese número de alquileres

select title, count(rental_id) alquileres from film 
join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
group by film.film_id
having alquileres=(
	select count(rental_id) total from film 
	join inventory on film.film_id=inventory.film_id
	join rental on inventory.inventory_id=rental.inventory_id
	group by film.film_id
	order by total desc
	limit 1);
    
-- El título de la película que más dinero ha dado (en suma de importe)

-- Solución decente pero no del todo correcta
select title, sum(amount) importe from film 
join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
join payment on rental.rental_id=payment.rental_id
group by film.film_id
order by importe desc
limit 1;

-- SOlución teniendo en cuenta empates

select title, sum(amount) importe from film 
join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id
join payment on rental.rental_id=payment.rental_id
group by film.film_id
having importe=(
	select sum(amount) importe from film 
	join inventory on film.film_id=inventory.film_id
	join rental on inventory.inventory_id=rental.inventory_id
	join payment on rental.rental_id=payment.rental_id
	group by film.film_id
	order by importe desc
	limit 1);
    
-- Los 5 actores que han trabajado en menos películas

select actor.*, count(film_id) peliculas from actor 
join film_actor on actor.actor_id=film_actor.actor_id
group by actor_id
order by peliculas
limit 5;

-- Posible otra solución también imperfecta pero quizá más correcta

select actor.*, count(film_id) peliculas from actor 
join film_actor on actor.actor_id=film_actor.actor_id
group by actor.actor_id
having peliculas<=(
	select max(peliculas) from (
		select count(film_id) peliculas from actor 
		join film_actor on actor.actor_id=film_actor.actor_id
		group by actor.actor_id
		order by peliculas
		limit 5) as temp
        );
        
-- Encontrar los clientes que hayan gastado más de 100 dólares

select customer.*, sum(amount) total from customer 
join payment on customer.customer_id=payment.customer_id
group by customer_id
having total>100;

-- Actores que no hayan trabajado en películas para niños o familiares

-- Consulta de división: primero miro los que sí y luego los que no
select * from actor
where actor_id not in (
	select actor_id from category
	join film_category on category.category_id=film_category.category_id
	join film on film_category.film_id=film.film_id
	join film_actor on film.film_id=film_actor.film_id
	where name in ('children','family'));

-- Actores que hayan trabajado en películas que no se hayan alquilado nunca

select distinct actor.* from actor join film_actor on actor.actor_id=film_actor.actor_id
where film_id not in (
select film.film_id from film join inventory on film.film_id=inventory.film_id
join rental on inventory.inventory_id=rental.inventory_id);

select distinct actor.* from actor 
join film_actor on actor.actor_id=film_actor.actor_id
join film on film_actor.film_id=film.film_id
left join inventory on film.film_id=inventory.film_id
left join rental on inventory.inventory_id=rental.inventory_id
where rental_id is null;

-- Clientes que han hecho un total de pagos por encima de la media.
-- Divide y vencerás
-- Calculo el total de pagos po cliente
select customer.*, sum(amount) pagos from customer
join payment on customer.customer_id=payment.customer_id
group by customer_id;
-- calculo la media sobre esta consulta
select avg(pagos) media from (
select customer.*, sum(amount) pagos from customer
join payment on customer.customer_id=payment.customer_id
group by customer_id) as temp;
-- filtro los clientes cuyo total esté por encima de la media
select customer.*, sum(amount) pagos from customer
join payment on customer.customer_id=payment.customer_id
group by customer_id
having pagos>(select avg(pagos) media from (
	select customer.*, sum(amount) pagos from customer
	join payment on customer.customer_id=payment.customer_id
	group by customer_id) as temp);

-- con vistas
create view pagos_por_cliente as
select customer.*, sum(amount) pagos from customer
join payment on customer.customer_id=payment.customer_id
group by customer_id;

select * from pagos_por_cliente
where pagos>(select avg(pagos) media from pagos_por_cliente);

-- Clientes que no han alquilado documentales (‘documentary’)
-- Primero los que alquilan

select * from customer join rental using (customer_id)
join inventory using (inventory_id)
join film using (film_id)
join film_category using (film_id)
join category using (category_id)
where name='documentary';

-- Pues esos no, los otros
select * from customer
where customer_id not in (
	select customer_id from customer join rental using (customer_id)
	join inventory using (inventory_id)
	join film using (film_id)
	join film_category using (film_id)
	join category using (category_id)
	where name='documentary');