[Projeto] Mão na Massa [1-8]

Respondi as questões usando PostgreSQL, por isso podem haver significativas diferenças na escrita do código, as quero apresentar as minhas soluções dos exercícios do 1 ao 8:

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

select count(*) from clientes;

2 - Quantos produtos foram vendidos no ano de 2022?

select count(*) as qntd_produtos_vendidos from (
    select
        produto_id
    from itens_venda
    group by produto_id
);

3 - Qual a categoria que mais vendeu em 2022?

select
    nome_categoria as categoria_mais_vendeu_2022
from (
    select
        c.nome_categoria,
        count(iv.venda_id) as qtd_vendas
    from produtos p
    join itens_venda iv on iv.produto_id = p.id_produto
    join categorias c on c.id_categoria = p.categoria_id
    join vendas v on v.id_venda = iv.venda_id
    where extract(year from v.data_venda) = 2022
    group by c.nome_categoria
    order by qtd_vendas desc
    limit 1
);

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

-- Trouxe as duas tabelas e colunas que contém datas
select * from (
    select
        extract(year from data_venda) as ano
    from vendas
    group by ano
    order by ano
    limit 1
) union (
    select
        extract(year from data_estoque) as ano
    from produtos
    group by ano
    order by ano
    limit 1
);

5 - Qual o nome do fornecedor que mais vendeu no primeiro ano disponível na base?
6 - Quanto ele vendeu no primeiro ano disponível na base de dados?

-- Fazendo a questão 5 acabei trazendo dados que respodem a questão 6. Também parti do pressuposto de que, por mais que eu saiba o ano mais antigo, a minha query tem que trazer essa informação a ser usada.

with cte_ano_mais_antigo_base as (
    select
        extract(year from data_venda) as ano_mais_antigo_base
    from vendas
    group by ano_mais_antigo_base
    order by ano_mais_antigo_base
    limit 1
),
cte_fornecedor_vendas_ano as (
    select
        f.nome as nome_fornecedor,
        extract(year from v.data_venda) as ano,
        count(v.id_venda) 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
    group by f.nome, ano
)
select
    mab.ano_mais_antigo_base,
    fva.nome_fornecedor,
    fva.qtd_vendas
from cte_fornecedor_vendas_ano fva, cte_ano_mais_antigo_base mab
where mab.ano_mais_antigo_base = fva.ano
order by qtd_vendas desc
limit 1;

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

select
    nome_categoria as categoria_mais_vendas
from (
    select
        c.nome_categoria,
        count(iv.venda_id) as qtd_vendas
    from produtos p
    join itens_venda iv on iv.produto_id = p.id_produto
    join categorias c on c.id_categoria = p.categoria_id
    join vendas v on v.id_venda = iv.venda_id
    group by c.nome_categoria
    order by qtd_vendas desc
    limit 2
);

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

with cte_joins_tabelas as ( -- Como os nomes não são repetidos, pude relacionar tudo sem problemas numa única CTE.
    select
        *
    from produtos p
    join itens_venda iv on iv.produto_id = p.id_produto
    join categorias c on c.id_categoria = p.categoria_id
    join vendas v on v.id_venda = iv.venda_id
),
cte_categorias_mais_vendas as ( -- Identifiquei primeiro as categorias mais vendidas
    select
        nome_categoria,
        count(venda_id) as qtd_vendas
    from cte_joins_tabelas
    group by nome_categoria
    order by qtd_vendas desc
    limit 2
),
cte_qntd_vendas_categorias as ( -- Aqui eu separei as vendas por ano
    select
        nome_categoria,
        count(venda_id) as qtd_vendas,
        extract(year from data_venda) as ano
    from cte_joins_tabelas
    group by nome_categoria, ano
    order by qtd_vendas desc
)
select
    qvc.nome_categoria,
    sum(case when qvc.ano = 2020 then qvc.qtd_vendas end) as vendas_2020,
    sum(case when qvc.ano = 2021 then qvc.qtd_vendas end) as vendas_2021,
    sum(case when qvc.ano = 2022 then qvc.qtd_vendas end) as vendas_2022,
    sum(case when qvc.ano = 2023 then qvc.qtd_vendas end) as vendas_2023
from cte_qntd_vendas_categorias qvc, cte_categorias_mais_vendas cmv
where cmv.nome_categoria = qvc.nome_categoria -- Aqui montei a tabela final considerando os dados das categorias em comum com as duas CTEs anteriores
group by qvc.nome_categoria;