[Projeto] Aula 05 - Mão na massa: hora da prática

Olá, seguem meus códigos para solução das atividades propostas:

-- 01. 

SELECT count(*) as Numero_Clientes from clientes;

-- 02. 

SELECT COUNT(iv.produto_id) as Qtd_Produtos_Vendidos_2022
from itens_venda as iv
join vendas as v on v.id_venda = iv.venda_id
where strftime('%Y', v.data_venda) = '2022'
;

-- 03. 

SELECT strftime('%Y', v.data_venda) as Ano, c.nome_categoria as Nome_Categoria, count(iv.produto_id) as Qtd_Vendas
from vendas as v
JOIN itens_venda as iv on iv.venda_id = v.id_venda
JOIN produtos as p on p.id_produto = iv.produto_id
join categorias as c on c.id_categoria = p.categoria_id
where Ano = '2022'
group by Nome_Categoria
order by Qtd_Vendas DESC
LIMIT 1
;

-- 4. 

select DISTINCT strftime('%Y', data_venda) as Ano
from vendas
order by Ano
LIMIT 1
;

-- 5. 

SELECT f.nome as Nome_Fornecedor, count(iv.produto_id) as Qtd_Vendas
from vendas as v
JOIN itens_venda as iv on iv.venda_id = v.id_venda
JOIN produtos as p on p.id_produto = iv.produto_id
join fornecedores as f on f.id_fornecedor = p.fornecedor_id
where strftime('%Y', v.data_venda) = 
  (select DISTINCT strftime('%Y', data_venda) as Ano
  from vendas
  order by Ano
  LIMIT 1)
group by Nome_Fornecedor
order by Qtd_Vendas DESC
LIMIT 1
;

-- 6. Mesmo código da questão 5, já tem essa informação da quantidadecategorias

-- 7. 

SELECT c.nome_categoria as Nome_Categoria, count(iv.produto_id) as Qtd_Vendas
from vendas as v
JOIN itens_venda as iv on iv.venda_id = v.id_venda
JOIN produtos as p on p.id_produto = iv.produto_id
join categorias as c on c.id_categoria = p.categoria_id
group by Nome_Categoria
order by Qtd_Vendas DESC
LIMIT 2
;

-- 8. 

SELECT Nome_Categoria, 
SUM(CASE when Ano = '2020' then Qtd_Vendas else 0 end) as Qtd_Vendas_2020,
SUM(CASE when Ano = '2021' then Qtd_Vendas else 0 end) as Qtd_Vendas_2021,
SUM(CASE when Ano = '2022' then Qtd_Vendas else 0 end) as Qtd_Vendas_2022,
SUM(CASE when Ano = '2023' then Qtd_Vendas else 0 end) as Qtd_Vendas_2023,
sum(Qtd_Vendas) as Qtd_Vendas_Totais
FROM
  (SELECT c.nome_categoria as Nome_Categoria, count(iv.produto_id) as Qtd_Vendas, strftime('%Y', v.data_venda) AS Ano
  from vendas as v
  JOIN itens_venda as iv on iv.venda_id = v.id_venda
  JOIN produtos as p on p.id_produto = iv.produto_id
  join categorias as c on c.id_categoria = p.categoria_id
  group by Nome_Categoria, Ano)
group by Nome_Categoria
order by Qtd_Vendas_Totais DESC
LIMIT 2
;

-- 9. 

WITH
  Vendas_Por_Categoria as
    (SELECT strftime('%Y', v.data_venda) as Ano, c.nome_categoria as Nome_Categoria, count(iv.produto_id) as Qtd_Vendas
    from vendas as v
    JOIN itens_venda as iv on iv.venda_id = v.id_venda
    JOIN produtos as p on p.id_produto = iv.produto_id
    join categorias as c on c.id_categoria = p.categoria_id
    where Ano = '2022'
    group by Nome_Categoria
    order by Qtd_Vendas DESC),
  Vendas_Totais AS
      (SELECT strftime('%Y', v.data_venda) as Ano, count(*) as Qtd_Vendas_Totais
    from vendas as v
    JOIN itens_venda as iv on iv.venda_id = v.id_venda
    where Ano = '2022')
  SELECT 
  vpa.Nome_Categoria, 
  vpa.Qtd_Vendas, 
  round(100.0*vpa.Qtd_Vendas/va.Qtd_Vendas_Totais,2) || '%' as Porcentagem
  from Vendas_Por_Categoria as vpa, Vendas_Totais as va
;
  
-- 10. 

WITH
  Melhor_Categoria as
    (SELECT c.nome_categoria as Categoria, count(iv.produto_id) as Qtd_Vendas
    from vendas as v
    JOIN itens_venda as iv on iv.venda_id = v.id_venda
    JOIN produtos as p on p.id_produto = iv.produto_id
    join categorias as c on c.id_categoria = p.categoria_id
    where strftime('%Y', v.data_venda) = '2022'
    group by Categoria
    order by Qtd_Vendas DESC
    LIMIT 1),
  Pior_Categoria as
    (SELECT c.nome_categoria as Categoria, count(iv.produto_id) as Qtd_Vendas
    from vendas as v
    JOIN itens_venda as iv on iv.venda_id = v.id_venda
    JOIN produtos as p on p.id_produto = iv.produto_id
    join categorias as c on c.id_categoria = p.categoria_id
    where strftime('%Y', v.data_venda) = '2022'
    group by Categoria
    order by Qtd_Vendas ASC
    LIMIT 1)
  SELECT
  mc.Categoria as Melhor_Categoria,
  mc.Qtd_Vendas as Qtd_Vendas_Melhor_Categoria,
  pc.Categoria as Pior_Categoria,
  pc.Qtd_Vendas as Qtd_Vendas_Pior_Categoria,
  round(((mc.Qtd_Vendas - pc.Qtd_Vendas)/pc.Qtd_Vendas)*100.0,2) || '%' as Porcentagem_Melhor_Relacao_Pior
  from Melhor_Categoria as mc, Pior_Categoria as pc
;