-- 1 - entregadores que não fizeram nenhuma entrega naquela data FUNCIONANDO
    SELECT DISTINCT e.nome
        FROM entregadores e inner join pedidos p
        on e.idEntregador = p.idEntregador
        WHERE p.idEntregador not in (SELECT DISTINCT COALESCE(pd.idEntregador, 0) FROM
                                     pedidos pd WHERE pd.dataPedido = '2017-11-02');

-- 2 - clientes que foram cadastrados mas não compraram FUNCIONANDO
    SELECT c.nome
        FROM clientes c LEFT JOIN pedidos p
        on c.idCliente = p.idCliente
        WHERE p.idCliente IS NULL;

-- 3 - item mais vendido por ordem FUNCIONANDO
SELECT * FROM (
    SELECT c.nome as nome, COUNT(pc.idComida) as 'qt comprada'
        FROM comidas c left join pedidoscomidas pc
            on c.idComida = pc.idComida
        GROUP BY c.nome

        UNION ALL

    SELECT b.nome as 'NOME', COUNT(pb.idBebida) as 'qt comprada'
        FROM bebidas b left join pedidosbebidas pb
            on b.idBebida = pb.idBebida
        GROUP BY b.nome
) AS y ORDER BY 2 DESC;

-- 4 - preço total de cada pedido funcionando
SELECT idPedido, sum(valor) from (
    SELECT p.idPedido, COALESCE(b.valor, 0) AS 'valor'
        FROM pedidos p inner join pedidosbebidas pb on p.idPedido = pb.idPedido
                       inner join bebidas b on b.idBebida = pb.idBebida

        UNION ALL

    SELECT p.idPedido, COALESCE(c.valor, 0) AS 'valor'
        FROM pedidos p inner join pedidoscomidas pc on p.idPedido = pc.idPedido
                       inner join comidas c on c.idComida = pc.idComida
) AS x GROUP BY idPedido ORDER BY sum(valor) DESC;

-- 5 - salário médio de cada entregador em tal período funciona
SELECT DISTINCT e.nome, ((e.precoEntrega * count(p.idEntregador)) / datediff('2017-11-06', '2017-11-01')) as 'salario diario'
    FROM entregadores e inner join pedidos p
        on e.idEntregador = p.idEntregador
    WHERE p.dataPedido between '2017-11-01' and '2017-11-06'
GROUP BY e.nome ORDER BY 2 DESC;

-- 6 - quantas vezes um entregador entregou em tal data FUNCIONANDO
SELECT DISTINCT COALESCE(e.nome, 'não há'), count(p.idEntregador)
    FROM entregadores e inner join pedidos p
        on e.idEntregador = p.idEntregador
    WHERE p.dataPedido = '2017-11-02'
GROUP BY e.nome ORDER BY 2;

-- 7 - clientes que mais gastaram dinheiro FUNCIONANDO
SELECT nome, sum(valor) from (
    SELECT cli.nome as nome, COALESCE(b.valor, 0) AS 'valor'
        FROM pedidos p inner join clientes cli on p.idCliente = cli.idCliente
                       inner join pedidosbebidas pb on p.idPedido = pb.idPedido
                       inner join bebidas b on b.idBebida = pb.idBebida

        UNION ALL

    SELECT cli.nome as nome, COALESCE(c.valor, 0) AS 'valor'
        FROM pedidos p inner join clientes cli on p.idCliente = cli.idCliente
                       inner join pedidoscomidas pc on p.idPedido = pc.idPedido
                       inner join comidas c on c.idComida = pc.idComida
) AS x GROUP BY nome ORDER BY sum(valor) DESC;

-- 8 - cliente que pediu mais vezes FUNCIONANDO
SELECT DISTINCT c.nome, count(p.idCliente)
    FROM clientes c inner join pedidos p
        on c.idCliente = p.idCliente
GROUP BY c.nome ORDER BY 2 DESC;