CREATE table taxa_homicidios
AS
SELECT
    sim.obito, 
    pnad.pop_ibge,
    pnad.pop_pnad,
    pnad.pop_filtrada as filtro_pnad,
    cast((cast(pop_ibge as float)/cast(pop_pnad as float))*cast(pop_filtrada as float) as integer) as filtro_ibge,
    cast(obito as float)*100000/((cast(pop_ibge as float)/cast(pop_pnad as float))*cast(pop_filtrada as float)) as taxa_homicidio,
    sim.ano_obito as ano,
    sim."ocor_SIGLA_UF" as uf,
    sim.idade_obito_anos,
    sim.def_raca_cor as raca_cor
FROM
    (
        select 
            count(*) as obito, 
            ano_obito,
            "ocor_CODIGO_UF",
            "ocor_SIGLA_UF",
            def_raca_cor,
            idade_obito_anos,
            "RACACOR"
        from sim 
        where "CIRCOBITO" = 3
        group by 
            ano_obito,
            "ocor_CODIGO_UF",
            "ocor_SIGLA_UF",
            def_raca_cor,
            idade_obito_anos,
            "RACACOR"
    ) as sim
INNER JOIN
    (
    select 
        pnad.ano_censo,
        pnad.cod_uf,
        pnad.cor_raca,
        pop.pop_ibge,
        pop.estado_id,
        pnad.pop_pnad,
        pnad.pop_filtrada  
    from 
        -- população por cor raça do PNAD
        (
            select 
                pnad_cont.ano_censo, 
                pnad_cont.cod_uf, 
                pnad_cont.cor_raca, 
                pnd.pop_pnad, 
                count(*) as pop_filtrada 
            from 
                pnad_cont 
                    inner join
                (
                    select 
                        ano_censo, 
                        cod_uf, 
                        count(*) as pop_pnad 
                    from pnad_cont 
                    group by 
                        ano_censo, 
                        cod_uf
                ) as pnd
                on 
                    pnad_cont.ano_censo = pnd.ano_censo and
                    pnad_cont.cod_uf = pnd.cod_uf
            group by 
                pnad_cont.ano_censo, 
                pnad_cont.cod_uf, 
                pnad_cont.cor_raca, 
                pnd.pop_pnad
        ) as pnad

    inner join
        -- População por estado 
        (
            select 
                ibge_populacao.estado_id, 
                ibge_populacao.ano_censo, 
                sum(ibge_populacao.populacao) as pop_ibge 
            from 
                ibge_populacao 
            group by 
                ibge_populacao.estado_id, 
                ibge_populacao.ano_censo
        ) as pop
    on 
        pnad.ano_censo = pop.ano_censo and
        pnad.cod_uf = pop.estado_id
    group by 
        pnad.ano_censo,
        pnad.cod_uf,
        pnad.cor_raca,
        pop.pop_ibge,
        pop.estado_id,
        pnad.pop_pnad,
        pnad.pop_filtrada
    ) as pnad
ON
    sim.ano_obito = pnad.ano_censo and
    sim."ocor_CODIGO_UF" = pnad.cod_uf and
    sim."RACACOR" = pnad.cor_raca
group by
    sim.ano_obito,
    sim.obito, pnad.pop_filtrada,
    sim."ocor_SIGLA_UF",
    sim.def_raca_cor,
    sim.idade_obito_anos,
    pnad.pop_ibge,
    pnad.pop_pnad
order by
    sim."ocor_SIGLA_UF",
    sim.ano_obito

;
