[Projeto] Resolução: Hora da Prática

Confesso que alguns desafios foram bem complicados, mas consegui.
E alguns até estão diferentes do professor.

-- 01 - Qual é o número de Clientes que existem na base de dados ?
SELECT count(id_cliente) as Qtd_Total_Clientes
FROM clientes;

-- 02 - Quantos produtos foram vendidos no ano de 2022 ?
SELECT count(iv.produto_id) as Qtd_Produtos_Vendidos
from vendas v
join itens_venda iv on iv.venda_id = v.id_venda
WHERE STRFTIME('%Y', v.data_venda) = '2022';

-- 03 - Qual a categoria que mais vendeu em 2022 ?
SELECT COUNT(iv.produto_id) as Qtd_Vendas, c.nome_categoria as Categoria
from vendas v
INNER join itens_venda iv on iv.venda_id = v.id_venda
INNER JOIN produtos p on p.id_produto = iv.produto_id
INNER JOIN categorias c on c.id_categoria = p.categoria_id
WHERE STRFTIME('%Y', v.data_venda) = '2022'
GROUP by c.nome_categoria
ORDER by Qtd_Vendas DESC
LIMIT 1;

-- 04 - Qual o primeiro ano disponível na base ?
SELECT MIN(STRFTIME('%Y', data_venda)) as Ano
FROM vendas;

-- 05 - Qual o nome do fornecedor que mais vendeu no primeiro ano disponível na base ?
SELECT f.nome as Fornecedor, COUNT(*) as Qtd_Vendas
FROM itens_venda iv
INNER join vendas v on iv.venda_id = v.id_venda
INNER JOIN produtos p on p.id_produto = iv.produto_id
INNER JOIN fornecedores f on f.id_fornecedor = p.fornecedor_id
WHERE STRFTIME('%Y', data_venda) = '2020'
GROUP BY Fornecedor
order by Qtd_Vendas DESC
limit 1;

-- 06 - Quanto ele vendeu no primeiro ano disponível na base de dados ?
SELECT f.nome as Fornecedor, COUNT(*) as Qtd_Vendas, STRFTIME('%Y', data_venda) as Ano
FROM itens_venda iv
INNER join vendas v on iv.venda_id = v.id_venda
INNER JOIN produtos p on p.id_produto = iv.produto_id
INNER JOIN fornecedores f on f.id_fornecedor = p.fornecedor_id
WHERE Ano = '2020'
GROUP BY Fornecedor
order by Qtd_Vendas DESC
limit 1;

-- 07 - Quais as duas categorias que mais venderam no total de todos os anos ?
SELECT COUNT(*) as Qtd_Vendas, c.nome_categoria as Categoria
FROM itens_venda iv
INNER JOIN produtos p on p.id_produto = iv.produto_id
INNER JOIN categorias c on c.id_categoria = p.categoria_id
GROUP by Categoria
ORDER by Qtd_Vendas DESC
LIMIT 2;

-- 08 - Crie uma tabela comparando as vendas ao longo do tempo das duas categorias que mais venderam no total de todos os anos.
SELECT "Ano/Mês",
SUM(CASE WHEN Categoria = 'Eletrônicos' THEN Qtd_Vendas ELSE 0 END) AS Eletrônicos, 
SUM(CASE WHEN Categoria = 'Vestuário' THEN Qtd_Vendas ELSE 0 END) AS Vestuário
FROM(
    SELECT COUNT(*) AS Qtd_Vendas, strftime('%Y/%m', v.data_venda) AS "Ano/Mês", c.nome_categoria AS Categoria
    FROM itens_venda iv
    JOIN vendas v ON v.id_venda = iv.venda_id
    JOIN PRODUTOS p ON p.id_produto = iv.produto_id
    JOIN CATEGORIAS c ON c.id_categoria = p.categoria_id
    WHERE Categoria IN ('Eletrônicos', 'Vestuário')
    GROUP BY "Ano/Mês", Categoria
    ORDER BY "Ano/Mês", Categoria
)
GROUP BY "Ano/Mês";

-- 09 - Calcule a porcentagem de vendas por categorias no ano de 2022.
SELECT Nome_Categoria, Qtd_Vendas, ROUND(100.0 * Qtd_Vendas / (SELECT count(*) From itens_venda),2) || '%' as Porcentagem
From (
  SELECT c.nome_categoria as Nome_Categoria, COUNT(iv.produto_id) as Qtd_Vendas, STRFTIME('%Y', v.data_venda) as Ano
  from itens_venda iv
  INNER JOIN vendas v on v.id_venda = iv.venda_id
  INNER JOIN produtos p on p.id_produto = iv.produto_id
  INNER JOIN categorias c on c.id_categoria = p.categoria_id
  WHERE Ano = '2022'
  GROUP by Nome_Categoria
  ORDER by Qtd_Vendas DESC
);

-- 10 - Crie uma métrica mostrando a porcentagem de vendas a mais que a melhor categoria tem em relação a pior no ano de 2022.
with Pior_Categoria as (
    SELECT Qtd_Vendas as Pior_Categoria_2022
    From (
      SELECT c.nome_categoria as Nome_Categoria, COUNT(iv.produto_id) as Qtd_Vendas
      from itens_venda iv
      INNER JOIN vendas v on v.id_venda = iv.venda_id
      INNER JOIN produtos p on p.id_produto = iv.produto_id
      INNER JOIN categorias c on c.id_categoria = p.categoria_id
      WHERE STRFTIME('%Y', v.data_venda) = '2022'
      GROUP by Nome_Categoria
      ORDER by Qtd_Vendas
      LIMIT 1)
), Melhor_Categoria as (
    SELECT Qtd_Vendas as Melhor_Categoria_2022
    From (
      SELECT c.nome_categoria as Nome_Categoria, COUNT(iv.produto_id) as Qtd_Vendas
      from itens_venda iv
      INNER JOIN vendas v on v.id_venda = iv.venda_id
      INNER JOIN produtos p on p.id_produto = iv.produto_id
      INNER JOIN categorias c on c.id_categoria = p.categoria_id
      WHERE STRFTIME('%Y', v.data_venda) = '2022'
      GROUP by Nome_Categoria
      ORDER by Qtd_Vendas DESC
      LIMIT 1)
)
SELECT mc.Melhor_Categoria_2022, pc.Pior_Categoria_2022,
((mc.Melhor_Categoria_2022 - pc.Pior_Categoria_2022) / pc.Pior_Categoria_2022 * 100.0) || '%' AS Porcentagem
from Melhor_Categoria mc, Pior_Categoria pc;