11 - Mão na massa: hora da prática (Resolvido)

Segue a resolução dos exercicios propostos:

-- 01 - Qual é o número de Clientes que existem na base de dados ? 10.000

SELECT COUNT(id_cliente) AS TOTAL_CLIENTES FROM CLIENTES;

-- 02 - Quantos produtos foram vendidos no ano de 2022 ? 54221

SELECT COUNT(iv.produto_id) AS Qtd_Produtos_Vendidos
FROM vendas v
JOIN itens_venda iv ON v.id_venda = iv.venda_id
WHERE strftime('%Y', v.data_venda) = '2022';

-- 03 - Qual a categoria que mais vendeu em 2022 ? LIVROS

SELECT C.nome_categoria, COUNT(V.id_venda) AS TOTAL
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
INNER JOIN vendas V ON V.id_venda = IV.venda_id
WHERE STRFTIME('%Y',V.data_venda) = '2022'
GROUP BY C.nome_categoria
ORDER BY TOTAL DESC;

-- 04 - Qual o primeiro ano disponível na base ? 2020

SELECT STRFTIME('%Y',data_venda) AS "ANO" FROM vendas
GROUP BY "ANO";

-- 05 - Qual o nome do fornecedor que mais vendeu no primeiro ano disponível na base ? HorizonDistributors

SELECT F.nome AS NOME_FORNECEDOR, COUNT(*) as Qtd_Vendas
FROM itens_venda IV
INNER JOIN produtos P ON P.id_produto = IV.produto_id
INNER JOIN fornecedores F ON F.id_fornecedor = p.fornecedor_id
INNER JOIN vendas V ON V.id_venda = IV.venda_id
WHERE STRFTIME('%Y',V.data_venda) = '2020'
GROUP BY NOME_FORNECEDOR
ORDER BY Qtd_Vendas DESC;

-- 06 - Quanto ele vendeu no primeiro ano disponível na base de dados ? 5093

SELECT F.nome AS NOME_FORNECEDOR, COUNT(V.id_venda) AS TOTAL_PRODUTOS, SUM(V.total_venda) AS VALOR_TOTAL
FROM itens_venda IV
INNER JOIN produtos P ON P.id_produto = IV.produto_id
INNER JOIN fornecedores F ON F.id_fornecedor = P.fornecedor_id
INNER JOIN vendas V ON V.id_venda = IV.venda_id
WHERE STRFTIME('%Y',V.data_venda) = '2020'
GROUP BY NOME_FORNECEDOR
ORDER BY TOTAL_PRODUTOS DESC;

-- 07 - Quais as duas categorias que mais venderam no total de todos os anos ? Livros e Vestuário

SELECT C.nome_categoria AS NOME_CATEGORIA, COUNT(iv.produto_id) AS TOTAL_PRODUTOS, SUM(iv.venda_id) AS VALOR_TOTAL
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
INNER JOIN vendas V ON V.id_venda = IV.venda_id
GROUP BY NOME_CATEGORIA
ORDER BY TOTAL_PRODUTOS DESC;


-- 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_MES",
SUM(Case when NOME_CATEGORIA=='Livros' Then TOTAL_PRODUTOS else 0 end) as Livros,
SUM(Case when NOME_CATEGORIA=='Vestuário' Then TOTAL_PRODUTOS else 0 end) as Vestuario
FROM(
  SELECT STRFTIME('%Y-%m',V.data_venda) AS "ANO_MES",
  C.nome_categoria AS NOME_CATEGORIA, COUNT(iv.produto_id) AS TOTAL_PRODUTOS
  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
  INNER JOIN vendas V ON V.id_venda = IV.venda_id
  WHERE NOME_CATEGORIA = 'Livros' or NOME_CATEGORIA = 'Vestuário'
  GROUP BY NOME_CATEGORIA, "ANO_MES"
  ORDER BY TOTAL_PRODUTOS DESC
  )
GROUP BY "ANO_MES";

-- 09 - Calcule a porcentagem de vendas por categorias no ano de 2022.

WITH Total_Vendas AS (
SELECT COUNT(*) as Total_Vendas_2022 
From itens_venda IV
JOIN vendas V ON v.id_venda = iv.venda_id
WHERE strftime('%Y', v.data_venda) = '2022'
)
SELECT NOME_CATEGORIA, TOTAL_PRODUTOS, ROUND(100.0*TOTAL_PRODUTOS/tv.Total_Vendas_2022, 2) || '%' AS Porcentagem
fROM(
  SELECT C.nome_categoria AS NOME_CATEGORIA, COUNT(iv.produto_id) AS TOTAL_PRODUTOS
  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
  INNER JOIN vendas V ON V.id_venda = IV.venda_id
  WHERE STRFTIME('%Y', V.data_venda) = '2022'
  GROUP BY NOME_CATEGORIA
  ORDER BY TOTAL_PRODUTOS DESC
), Total_Vendas tv
;