Subconsultas

-- SUBCONSULTAS

-- Poner consultas detro de otras consultas
-- Se pueden poner en el WHERE, en el FROM y como campos

-- El importe máximo de un pago en mi tabla de pagos
select max(amount) from payment;

-- Quiero saber los clientes que han hecho pagos por la cantidad máxima
-- ¿Como lo puedo hacer? Usando una subconsulta
-- En el where (y también puede ser en el having)
-- No utilizo un valor fijo sino que uso otra consulta

select distinct first_name, last_name from customer
join payment on customer.customer_id=payment.customer_id
where amount=(select max(amount) from payment);

-- Puedo usar una subconsulta como un campo
select distinct first_name, last_name, 
(select count(*) from rental where customer.customer_id=rental.customer_id) alquileres
from customer;

-- Puedo usar una subconsulta como 'tabla virtual'
-- Es decir, yo creo una consulta y puedo seleccionar datos dentro de esa consulta
-- Por ejemplo ¿Cual es la media de alquileres por cliente?

-- Primero hago la consulta que me da el total de alquileres por cliente
select first_name,last_name, count(rental_id)  alquileres from customer join rental on customer.customer_id=rental.customer_id
group by customer.customer_id;

-- Después uso esa consulta como una subconsulta (subquery)

select avg(alquileres) from 
(select  count(rental_id)  alquileres from customer join rental on customer.customer_id=rental.customer_id
group by customer.customer_id)  temp;

-- ¿Qué clientes tienen alquileres por encima de la media?

select first_name,last_name, count(rental_id)  alquileres from customer join rental on customer.customer_id=rental.customer_id
group by customer.customer_id
having alquileres>(select avg(alquileres) from 
(select  count(rental_id)  alquileres from customer join rental on customer.customer_id=rental.customer_id
group by customer.customer_id)  temp);

-- Que clientes han pagado por encima de la media

-- Calcular el total de pago por cliente
select first_name, last_name, sum(amount) total
from customer
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id;

-- COn esto puedo calcular la media
select avg(total) media from (
select sum(amount) total
from customer
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id) temp;

-- después obtengo los clientes que superan esa media
select first_name, last_name, sum(amount) total
from customer
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id
having total>(select avg(total) media from (
select sum(amount) total
from customer
join payment on customer.customer_id=payment.customer_id
group by customer.customer_id) temp);