Soluciones ejercicios fechas

-- Mostrar los nombres de los clientes que han alquilado 
-- una película los fines de semana

select distinct first_name, last_name
from customer c join rental r on c.customer_id=r.customer_id -- Uso alias para evitar repetir el nombre de la tabla
where weekday(rental_date)>=5; -- weekday(rental_date) in (5,6) weekday(rental_date) between 5 and 6

-- Mostrar el total de pagos realizados en 2006

select sum(amount) total
from payment
where year(payment_date)=2006;

-- Mostrar las películas alquiladas en agosto de 2005

select distinct f.* from film f
join inventory i on f.film_id=i.film_id
join rental r on i.inventory_id=r.inventory_id
where year(rental_date)=2005 and month(rental_date)=8;

-- ¿En qué mes de qué año se han alquilado más películas?

select year(rental_date) anyo, month(rental_date) mes, count(rental_id) total
from rental
group by anyo,mes
order by total desc
limit 1;

-- si soy purista y quiero comprobar repetidos

select year(rental_date) anyo, month(rental_date) mes, count(rental_id) total
from rental
group by anyo,mes
having total=(
	select  count(rental_id) total
	from rental
	group by year(rental_date) , month(rental_date) 
	order by total desc
	limit 1
);

-- Hacer una consulta que nos muestre el total de pagos 
-- realizados entre semana y en fin de semana

select if(weekday(payment_date)<5,'Diario','Fin de semana') tipo, sum(amount) total
from payment
group by tipo;

select case 
	when weekday(payment_date)<5 then 'Diario'
	else 'Fin de semana'
end tipo, sum(amount) total
from payment
group by tipo;

-- En esta solución Jaume utiliza una subconsulta para crear una variable virtual
-- weekday_payment (los pagos de fin de semana)

SELECT SUM(`amount`) AS total_payments, weekday_payment, SUM(`amount`) - weekday_payment AS weekend_payment
FROM `payment`
JOIN (
-- Aquí se usa la subconsulta para crear una variable
    SELECT SUM(`amount`) AS weekday_payment
    FROM `payment`
    WHERE WEEKDAY(`payment_date`) < 5
) AS weekday_payment_tmp;