[Projeto] Mão na Massa [9 e 10]

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 9 e 10:

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

with cte_total_vendas as (
    select
        count(venda_id) as total_vendas
    from itens_venda iv
    join vendas v on v.id_venda = iv.venda_id
    where extract(year from v.data_venda) = 2022
),
cte_vendas_categoria as (
    select
        c.nome_categoria,
        count(iv.venda_id) as qtd_vendas
    from itens_venda iv
    join vendas v on v.id_venda = iv.venda_id
    join produtos p on iv.produto_id = p.id_produto
    join categorias c on p.categoria_id = c.id_categoria
    where extract(year from v.data_venda) = 2022
    group by c.nome_categoria
)
select
    nome_categoria,
    qtd_vendas,
    round(
        (qtd_vendas::numeric / total_vendas * 100.0), 2
    ) as porcentagem
from cte_total_vendas tv
cross join cte_vendas_categoria vc
order by porcentagem 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 cte_qtd_vendas_categorias as (
    select
        nome_categoria,
        count(venda_id) as qtd_vendas
    from (
        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
        where extract(year from v.data_venda) = 2022
    )
    group by nome_categoria
),
cte_categorias_ordenadas as ( -- Essa subquery usa o LAG() OVER() para encontrar o dado anterior da coluna que queremos calcular
    select
        nome_categoria,
        qtd_vendas,
        lag(qtd_vendas) over (order by qtd_vendas) as qtd_vendas_anterior
    from ( -- Essa subquery une as duas categorias, a mais e a menos vendida
        select * from (
            select
                nome_categoria,
                qtd_vendas
            from cte_qtd_vendas_categorias
            order by qtd_vendas desc
            limit 1
        )

        union all

        select * from (
            select
                nome_categoria,
                qtd_vendas
            from cte_qtd_vendas_categorias
            order by qtd_vendas asc
            limit 1
        )
    ) as uniao_categorias
    group by nome_categoria, qtd_vendas
    order by qtd_vendas desc
)
select
    nome_categoria,
    qtd_vendas,
    round(
        (qtd_vendas::numeric - qtd_vendas_anterior) / qtd_vendas_anterior * 100.0, 2
    ) as diferenca_percentual -- Achei mais interessante separar o cálculo numa nova query externa às CTEs montadas anteriormente, ficaria ainda mais verboso, apesar de ter ficado "maior".
from cte_categorias_ordenadas;