[Projeto] Hora da Prática: Aula 05 | SQLite online: análise de dados com SQL

Olá! Esse projeto deu um trabalhinho, mas o resultado final é recompensador. Atenção para a solicitação 02, na 'opinião do instrutor', a resolução traz a quantidade de vendas, mas ele pede a quantidade de produtos vendidos, por isso o código ficou diferente.

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

SELECT COUNT(*) FROM clientes;

-- Quantos produtos foram vendidos no ano de 2022 ? 

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

-- Qual a categoria que mais vendeu em 2022 ? 

SELECT c.nome_categoria, COUNT(iv.produto_id) AS Qtd_Vendas
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 strftime('%Y', v.data_venda) = '2022'
GROUP BY c.nome_categoria
ORDER BY Qtd_Vendas DESC
LIMIT 1;

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

SELECT MIN(strftime('%Y', data_venda)) FROM vendas;

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

SELECT f.nome, COUNT(iv.produto_id) AS Qtd_Vendas
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 fornecedores f ON f.id_fornecedor = p.fornecedor_id
WHERE strftime('%Y', v.data_venda) = '2020'
GROUP BY f.nome
ORDER BY Qtd_Vendas DESC
LIMIT 1;

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

SELECT f.nome, COUNT(iv.produto_id) AS Qtd_Vendas
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 fornecedores f ON f.id_fornecedor = p.fornecedor_id
WHERE strftime('%Y', v.data_venda) = '2020'
GROUP BY f.nome
ORDER BY Qtd_Vendas DESC
LIMIT 1;

-- Quais as duas categorias que mais venderam no total de todos os anos ? 

SELECT c.nome_categoria, COUNT(iv.produto_id) AS Qtd_Vendas
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
GROUP BY c.nome_categoria
ORDER BY Qtd_Vendas DESC
LIMIT 2;

-- Crie uma tabela comparando as vendas ao longo do tempo das duas categorias que mais venderam no total de todos os anos. 

WITH TopCategorias AS (
    SELECT c.id_categoria, c.nome_categoria
    FROM categorias c
    JOIN produtos p ON p.categoria_id = c.id_categoria
    JOIN itens_venda iv ON iv.produto_id = p.id_produto
    GROUP BY c.id_categoria, c.nome_categoria
    ORDER BY COUNT(iv.produto_id) DESC
    LIMIT 2
)
SELECT strftime('%Y', v.data_venda) AS Ano, c.nome_categoria, COUNT(iv.produto_id) AS Qtd_Vendas
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 c.id_categoria IN (SELECT id_categoria FROM TopCategorias)
GROUP BY Ano, c.nome_categoria
ORDER BY Ano, c.nome_categoria;

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

SELECT c.nome_categoria, COUNT(iv.produto_id) AS Qtd_Vendas, ROUND(100.0 * COUNT(iv.produto_id) / (SELECT COUNT(*) FROM itens_venda iv JOIN vendas v ON v.id_venda = iv.venda_id WHERE strftime('%Y', v.data_venda) = '2022'), 2) || '%' AS Porcentagem
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 strftime('%Y', v.data_venda) = '2022'
GROUP BY c.nome_categoria
ORDER BY Qtd_Vendas DESC;

-- 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 Vendas2022 AS (
    SELECT c.nome_categoria, COUNT(iv.produto_id) AS Qtd_Vendas
    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 strftime('%Y', v.data_venda) = '2022'
    GROUP BY c.nome_categoria
),
MelhorCategoria AS (
    SELECT Qtd_Vendas
    FROM Vendas2022
    ORDER BY Qtd_Vendas DESC
    LIMIT 1
),
PiorCategoria AS (
    SELECT Qtd_Vendas
    FROM Vendas2022
    ORDER BY Qtd_Vendas ASC
    LIMIT 1
)
SELECT ROUND(100.0 * (MelhorCategoria.Qtd_Vendas - PiorCategoria.Qtd_Vendas) / PiorCategoria.Qtd_Vendas, 2) || '%' AS Diferenca
FROM MelhorCategoria, PiorCategoria;

Olá, William. Tudo bem?

Parabéns pelo seu trabalho no projeto. Você demonstrou um bom entendimento das consultas SQL e aplicou de maneira eficaz as técnicas aprendidas.

Alguns pontos positivos do seu projeto:

Clareza e Organização: As consultas SQL estão bem organizadas e comentadas, facilitando a leitura e entendimento do código.
Resolução Completa: Você conseguiu resolver todas as questões propostas, inclusive a solicitação 02, onde foi necessária a distinção entre quantidade de vendas e quantidade de produtos vendidos.
Uso Eficiente de Funções SQL: Utilizou funções como COUNT, JOIN, GROUP BY, ORDER BY, LIMIT, ROUND, e WITH de forma correta e eficiente. Isso mostra seu entendimento sobre como manipular e analisar dados em SQL.
Criação de Tabelas Temporárias: O uso de CTEs (Common Table Expressions) para calcular a porcentagem de vendas e comparar vendas ao longo do tempo das duas categorias mais vendidas foi uma escolha inteligente e demonstra um bom nível de proficiência.

Seu projeto está muito bem desenvolvido e demonstra uma forte compreensão das ferramentas e técnicas necessárias para análise de dados com SQL. Continue assim, e parabéns pelo excelente trabalho!

Um abraço e bons estudos.

Mergulhe em programação