Definições dos indicadores (SQL)

WITH item_cod AS (
    SELECT ct.orgao_cnpj AS cnpj,
           ri.valor_unitario_homologado::float8 AS pago,
           least(coalesce(ic.quantidade, 1), 1000000)::float8 AS qtd,
           coalesce(
             CASE WHEN ic.codigo_catalogo IS NOT NULL AND ic.codigo_catalogo NOT IN ('.','','0')
                       AND char_length(ic.codigo_catalogo) >= 4 THEN ic.codigo_catalogo END,
             CASE WHEN cl.metodo = 'exato' OR cl.score >= 0.55 THEN cl.codigo END
           ) AS cod,
           coalesce(ic.material_ou_servico, 'M') AS tipo
    FROM resultado_item ri
    JOIN item_contratacao ic ON ic.id = ri.item_id
    JOIN contratacao ct ON ct.numero_controle_pncp = ic.contratacao_id
    LEFT JOIN item_classificacao cl ON cl.item_id = ic.id
    WHERE ri.valor_unitario_homologado > 0
      AND (ri.data_resultado IS NULL OR ri.data_resultado >= current_date - interval '24 months')
  ),
  med AS (
    SELECT cod, tipo, percentile_cont(0.5) WITHIN GROUP (ORDER BY pago) AS mediana
    FROM item_cod WHERE cod IS NOT NULL GROUP BY 1, 2 HAVING count(*) >= 8
  )
  SELECT i.cnpj,
         count(*)::int AS itens_comparados,
         count(*) FILTER (WHERE i.pago > m.mediana)::int AS itens_acima,
         coalesce(sum(greatest(i.pago - m.mediana, 0) * i.qtd) FILTER (WHERE i.pago <= 100 * m.mediana), 0) AS economia_potencial,
         coalesce(sum(m.mediana * i.qtd) FILTER (WHERE i.pago <= 100 * m.mediana), 0) AS base_comparada
  FROM item_cod i
  JOIN med m ON m.cod = i.cod AND m.tipo = i.tipo
  GROUP BY i.cnpj

Alimenta o painel: Painel de desperdício →

WITH base AS (
    SELECT g.cod_ibge, g.exercicio, g.ente, g.uf, g.populacao,
           g.pago, g.pago_per_capita,
           CASE WHEN g.populacao < 20000 THEN 'peq'
                WHEN g.populacao < 100000 THEN 'med'
                WHEN g.populacao < 500000 THEN 'gra'
                ELSE 'metro' END AS porte
    FROM gasto_por_ente_ano g
    WHERE g.esfera = 'M' AND g.pago_per_capita > 0 AND g.populacao > 0
  ),
  ranked AS (
    SELECT base.*,
           percent_rank() OVER (PARTITION BY uf, porte, exercicio ORDER BY pago_per_capita) AS percentil,
           count(*) OVER (PARTITION BY uf, porte, exercicio) AS n_pares
    FROM base
  ),
  grupo AS (
    SELECT uf, porte, exercicio,
           percentile_cont(0.5) WITHIN GROUP (ORDER BY pago_per_capita) AS mediana
    FROM base GROUP BY uf, porte, exercicio
  )
  SELECT r.cod_ibge, r.exercicio, r.ente, r.uf, r.porte, r.populacao,
         r.pago, r.pago_per_capita,
         round(gr.mediana::numeric, 2) AS mediana_pares,
         r.n_pares::int AS n_pares,
         round(r.percentil::numeric, 4) AS percentil,
         round(greatest(r.pago_per_capita - gr.mediana, 0)::numeric, 2) AS excesso_per_capita,
         round((greatest(r.pago_per_capita - gr.mediana, 0) * r.populacao)::numeric) AS excesso_total
  FROM ranked r
  JOIN grupo gr ON gr.uf = r.uf AND gr.porte = r.porte AND gr.exercicio = r.exercicio
  WHERE r.n_pares >= 5

Alimenta o painel: Despesa comparada →

WITH sanc AS (
    SELECT DISTINCT regexp_replace(cnpj_cpf, '\D', '', 'g') AS ni
    FROM sancao WHERE data_fim IS NULL OR data_fim >= current_date
  ),
  exposto AS (
    SELECT c.orgao_cnpj AS cnpj,
           sum(coalesce(c.valor_global, 0)) FILTER (WHERE s.ni IS NOT NULL) AS valor_sancionado,
           count(*) FILTER (WHERE s.ni IS NOT NULL)::int AS n_sancionado
    FROM contrato c
    LEFT JOIN sanc s ON s.ni = regexp_replace(c.fornecedor_ni, '\D', '', 'g')
    WHERE c.fornecedor_ni IS NOT NULL AND length(c.fornecedor_ni) = 14
      AND (c.vigencia_inicio IS NULL OR c.vigencia_inicio >= current_date - interval '24 months')
      AND coalesce(c.valor_global, 0) <= 10000000000
    GROUP BY c.orgao_cnpj
  )
  SELECT coalesce(ec.cnpj, ex.cnpj) AS cnpj,
         coalesce(ec.economia_potencial, 0) AS economia_potencial,
         coalesce(ec.itens_acima, 0) AS itens_acima,
         coalesce(ex.valor_sancionado, 0) AS valor_sancionado,
         coalesce(ex.n_sancionado, 0) AS n_sancionado
  FROM economia_ente ec
  FULL OUTER JOIN exposto ex ON ex.cnpj = ec.cnpj
  WHERE coalesce(ec.economia_potencial, 0) > 0 OR coalesce(ex.valor_sancionado, 0) > 0

Alimenta o painel: Painel de desperdício →

WITH agg AS (
    SELECT r.cod_ibge, r.exercicio, r.conta AS funcao,
           max(r.populacao) AS pop_ano,
           max(r.valor) FILTER (WHERE r.coluna LIKE 'DESPESAS LIQUIDADAS AT%') AS liquidada,
           max(r.valor) FILTER (WHERE r.coluna LIKE 'DESPESAS EMPENHADAS AT%') AS empenhada,
           max(r.valor) FILTER (WHERE r.coluna LIKE 'INSCRITAS EM RESTOS%') AS rp_nao_processados,
           max(r.valor) FILTER (WHERE r.coluna LIKE '%ATUALIZADA%') AS dotacao_atualizada
    FROM siconfi_rreo_funcao r
    WHERE r.cod_conta = 'RREO2TotalDespesas'
      AND r.periodo = 6
      AND r.conta IN (
        'Legislativa','Judiciária','Essencial à Justiça','Administração','Defesa Nacional',
        'Segurança Pública','Relações Exteriores','Assistência Social','Previdência Social',
        'Saúde','Trabalho','Educação','Cultura','Direitos da Cidadania','Urbanismo',
        'Habitação','Saneamento','Gestão Ambiental','Ciência e Tecnologia','Agricultura',
        'Organização Agrária','Indústria','Comércio e Serviços','Comunicações','Energia',
        'Transporte','Desporto e Lazer','Encargos Especiais','Reserva de Contingência')
    GROUP BY 1, 2, 3
  ),
  base AS (
    /* população do ANO do relatório (do próprio Bronze), com fallback à atual do /entes —
       usar a atual p/ todos os exercícios distorcia o per-capita (média 9,76% em 2023) e
       mudava a faixa de porte de ~119 municípios (achado da revisão adversarial). */
    SELECT a.*, e.ente, e.uf,
           coalesce(nullif(a.pop_ano, 0), e.populacao) AS populacao,
           a.liquidada / nullif(coalesce(nullif(a.pop_ano, 0), e.populacao), 0) AS liq_pc,
           CASE WHEN coalesce(nullif(a.pop_ano, 0), e.populacao) < 20000 THEN 'peq'
                WHEN coalesce(nullif(a.pop_ano, 0), e.populacao) < 100000 THEN 'med'
                WHEN coalesce(nullif(a.pop_ano, 0), e.populacao) < 500000 THEN 'gra'
                ELSE 'metro' END AS porte
    FROM agg a
    JOIN siconfi_ente e ON e.cod_ibge = a.cod_ibge
    WHERE e.esfera = 'M' AND coalesce(nullif(a.pop_ano, 0), e.populacao) > 0 AND a.liquidada > 0
  ),
  ranked AS (
    SELECT base.*,
           percent_rank() OVER (PARTITION BY uf, porte, exercicio, funcao ORDER BY liq_pc) AS percentil,
           count(*) OVER (PARTITION BY uf, porte, exercicio, funcao) AS n_pares
    FROM base
  ),
  grupo AS (
    SELECT uf, porte, exercicio, funcao,
           percentile_cont(0.5) WITHIN GROUP (ORDER BY liq_pc) AS mediana
    FROM base GROUP BY uf, porte, exercicio, funcao
  )
  SELECT r.cod_ibge, r.exercicio, r.funcao, r.ente, r.uf, r.porte, r.populacao,
         r.liquidada,
         round(r.liq_pc::numeric, 2) AS liquidada_per_capita,
         round(gr.mediana::numeric, 2) AS mediana_pares,
         r.n_pares::int AS n_pares,
         round(r.percentil::numeric, 4) AS percentil,
         round(greatest(r.liq_pc - gr.mediana, 0)::numeric, 2) AS excesso_per_capita,
         round((greatest(r.liq_pc - gr.mediana, 0) * r.populacao)::numeric) AS excesso_total,
         r.empenhada,
         round(greatest(coalesce(r.empenhada, 0) - coalesce(r.liquidada, 0), 0)::numeric, 2) AS gap_empenhado_liquidado,
         r.rp_nao_processados,
         r.dotacao_atualizada
  FROM ranked r
  JOIN grupo gr ON gr.uf = r.uf AND gr.porte = r.porte AND gr.exercicio = r.exercicio AND gr.funcao = r.funcao
  WHERE r.n_pares >= 5

Alimenta o painel: Despesa comparada (modo função) →

SELECT DISTINCT ON (i.id) i.id AS item_id,
         i.contratacao_id,
         c.orgao_cnpj,
         c.uf,
         c.esfera,
         c.municipio_nome,
         c.ano,
         i.descricao,
         m.substancia,
         m.dose,
         i.unidade_medida,
         r.quantidade,
         round(r.valor_unitario_homologado::numeric, 4) AS valor_unitario,
         round(m.teto_unitario::numeric, 4) AS teto_unitario,
         round((r.valor_unitario_homologado / m.teto_unitario)::numeric, 2) AS razao_sobre_teto,
         round((greatest(r.valor_unitario_homologado - m.teto_unitario, 0)
                * coalesce(nullif(r.quantidade, 0), 1))::numeric, 2) AS excedente,
         m.ggrem_teto,
         m.edicao AS edicao_cmed
  FROM item_cmed_match m
  JOIN item_contratacao i ON i.id = m.item_id
  JOIN resultado_item r ON r.item_id = i.id
  JOIN contratacao c ON c.numero_controle_pncp = i.contratacao_id
  WHERE m.casou
    AND m.teto_unitario > 0
    AND r.valor_unitario_homologado > m.teto_unitario * 1.05
    AND r.valor_unitario_homologado / m.teto_unitario <= 100
    AND coalesce(r.raw->>'situacaoCompraItemResultadoId', '1') = '1'
    AND c.ano >= 2024
  ORDER BY i.id, (greatest(r.valor_unitario_homologado - m.teto_unitario, 0)
                  * coalesce(nullif(r.quantidade, 0), 1)) DESC

Alimenta o painel: PMVG (teto de medicamento) →

WITH c AS (
    SELECT orgao_cnpj, fornecedor_ni, coalesce(valor_global, 0) AS val,
           EXISTS (
             SELECT 1 FROM sancao s
             WHERE s.cnpj_cpf = contrato.fornecedor_ni AND (s.data_fim IS NULL OR s.data_fim >= current_date)
           ) AS sancionado
    FROM contrato
    WHERE fornecedor_ni IS NOT NULL AND length(fornecedor_ni) = 14
      AND (vigencia_inicio IS NULL OR vigencia_inicio >= current_date - interval '24 months')
      AND coalesce(valor_global, 0) <= 10000000000
  ),
  contratos AS (
    SELECT orgao_cnpj AS cnpj,
           count(*)::int AS contratos_24m,
           count(*) FILTER (WHERE sancionado)::int AS contratos_sancionado,
           count(DISTINCT fornecedor_ni) FILTER (WHERE sancionado)::int AS sancionados_distintos,
           count(DISTINCT fornecedor_ni)::int AS num_fornecedores
    FROM c GROUP BY 1
  ),
  hhi AS (
    SELECT orgao_cnpj AS cnpj,
           round(sum(power(100.0 * fval / nullif(tot, 0), 2)))::int AS hhi
    FROM (
      SELECT orgao_cnpj, fornecedor_ni, sum(val) AS fval, sum(sum(val)) OVER (PARTITION BY orgao_cnpj) AS tot
      FROM c GROUP BY 1, 2
    ) f
    GROUP BY 1
  ),
  oflags AS (
    SELECT ct.orgao_cnpj AS cnpj,
           count(*) FILTER (WHERE o.razao_vs_mediana > 1 AND o.razao_vs_mediana <= 100)::int AS sobre,
           count(*) FILTER (WHERE o.razao_vs_mediana < 1)::int AS sub
    FROM outlier o
    JOIN resultado_item ri ON ri.id = o.resultado_item_id
    JOIN item_contratacao ic ON ic.id = ri.item_id
    JOIN contratacao ct ON ct.numero_controle_pncp = ic.contratacao_id
    GROUP BY 1
  ),
  aval AS (
    SELECT ct.orgao_cnpj AS cnpj, count(*)::int AS n
    FROM resultado_item ri
    JOIN item_contratacao ic ON ic.id = ri.item_id
    JOIN contratacao ct ON ct.numero_controle_pncp = ic.contratacao_id
    GROUP BY 1
  ),
  sinais AS (
    SELECT co.cnpj,
           co.contratos_24m, co.contratos_sancionado, co.sancionados_distintos, co.num_fornecedores,
           coalesce(hhi.hhi, 0) AS hhi,
           coalesce(oflags.sobre, 0) AS flags_sobre,
           coalesce(oflags.sub, 0) AS flags_sub,
           coalesce(aval.n, 0) AS itens_avaliados,
           coalesce(cl.pares_ligados, 0) AS conluio_pares,
           coalesce(cl.empresas_envolvidas, 0) AS conluio_empresas,
           -- densidades: NULL quando o denominador não sustenta o sinal
           CASE WHEN co.contratos_24m >= 5 THEN co.contratos_sancionado::float8 / co.contratos_24m END AS d_sanc,
           CASE WHEN coalesce(aval.n, 0) >= 10 THEN coalesce(oflags.sobre, 0)::float8 / aval.n END AS d_sobre,
           CASE WHEN coalesce(aval.n, 0) >= 10 THEN coalesce(oflags.sub, 0)::float8 / aval.n END AS d_sub,
           CASE WHEN co.num_fornecedores >= 3 THEN hhi.hhi END AS d_hhi,
           -- conluio: fração das empresas do órgão que estão em par ligado; só com ≥3 fornecedores
           CASE WHEN co.num_fornecedores >= 3
                THEN coalesce(cl.empresas_envolvidas, 0)::float8 / co.num_fornecedores END AS d_conluio
    FROM contratos co
    LEFT JOIN hhi ON hhi.cnpj = co.cnpj
    LEFT JOIN oflags ON oflags.cnpj = co.cnpj
    LEFT JOIN aval ON aval.cnpj = co.cnpj
    LEFT JOIN conluio_orgao cl ON cl.cnpj = co.cnpj
  ),
  -- percentil por sinal SÓ entre órgãos onde o sinal é mensurável
  p_sanc AS (SELECT cnpj, percent_rank() OVER (ORDER BY d_sanc) AS p FROM sinais WHERE d_sanc IS NOT NULL),
  p_conluio AS (SELECT cnpj, percent_rank() OVER (ORDER BY d_conluio) AS p FROM sinais WHERE d_conluio IS NOT NULL),
  p_sobre AS (SELECT cnpj, percent_rank() OVER (ORDER BY d_sobre) AS p FROM sinais WHERE d_sobre IS NOT NULL),
  p_sub AS (SELECT cnpj, percent_rank() OVER (ORDER BY d_sub) AS p FROM sinais WHERE d_sub IS NOT NULL),
  p_hhi AS (SELECT cnpj, percent_rank() OVER (ORDER BY d_hhi) AS p FROM sinais WHERE d_hhi IS NOT NULL)
  SELECT s.cnpj,
         s.contratos_24m,
         s.contratos_sancionado,
         s.sancionados_distintos,
         s.flags_sobre AS flags_sobrepreco,
         s.flags_sub AS flags_subpreco,
         s.itens_avaliados,
         s.num_fornecedores,
         s.hhi,
         s.conluio_pares,
         s.conluio_empresas,
         round(p_sanc.p::numeric, 4) AS p_sancao,
         round(p_conluio.p::numeric, 4) AS p_conluio,
         round(p_sobre.p::numeric, 4) AS p_sobrepreco,
         round(p_hhi.p::numeric, 4) AS p_hhi,
         round(p_sub.p::numeric, 4) AS p_subpreco,
         CASE
           WHEN (s.contratos_24m >= 5 OR s.itens_avaliados >= 10)
                AND (p_sanc.p IS NOT NULL OR p_conluio.p IS NOT NULL OR p_sobre.p IS NOT NULL OR p_hhi.p IS NOT NULL OR p_sub.p IS NOT NULL)
           THEN round(100 * (
             coalesce(0.30 * p_sanc.p, 0) + coalesce(0.30 * p_conluio.p, 0) + coalesce(0.22 * p_sobre.p, 0)
               + coalesce(0.10 * p_hhi.p, 0) + coalesce(0.08 * p_sub.p, 0)
           ) / nullif(
             (CASE WHEN p_sanc.p IS NOT NULL THEN 0.30 ELSE 0 END)
               + (CASE WHEN p_conluio.p IS NOT NULL THEN 0.30 ELSE 0 END)
               + (CASE WHEN p_sobre.p IS NOT NULL THEN 0.22 ELSE 0 END)
               + (CASE WHEN p_hhi.p IS NOT NULL THEN 0.10 ELSE 0 END)
               + (CASE WHEN p_sub.p IS NOT NULL THEN 0.08 ELSE 0 END),
             0
           ))::int
         END AS score
  FROM sinais s
  LEFT JOIN p_sanc ON p_sanc.cnpj = s.cnpj
  LEFT JOIN p_conluio ON p_conluio.cnpj = s.cnpj
  LEFT JOIN p_sobre ON p_sobre.cnpj = s.cnpj
  LEFT JOIN p_sub ON p_sub.cnpj = s.cnpj
  LEFT JOIN p_hhi ON p_hhi.cnpj = s.cnpj

Alimenta o painel: Sentinela →

WITH tot AS (
    SELECT d.cod_ibge, d.exercicio,
      max(d.valor) FILTER (WHERE d.coluna = 'Despesas Empenhadas') AS empenhado,
      max(d.valor) FILTER (WHERE d.coluna = 'Despesas Liquidadas') AS liquidado,
      max(d.valor) FILTER (WHERE d.coluna = 'Despesas Pagas') AS pago
    FROM siconfi_despesa d
    WHERE d.cod_conta = 'TotalDespesas'
    GROUP BY d.cod_ibge, d.exercicio
  )
  SELECT t.cod_ibge, t.exercicio, e.ente, e.uf, e.esfera, e.populacao,
    t.empenhado, t.liquidado, t.pago,
    CASE WHEN e.populacao > 0 THEN round(t.pago / e.populacao, 2) END AS pago_per_capita
  FROM tot t
  JOIN siconfi_ente e ON e.cod_ibge = t.cod_ibge

Alimenta o painel: Despesa comparada →

WITH forn_orgao AS (
    SELECT DISTINCT orgao_cnpj, fornecedor_ni
    FROM contrato
    WHERE fornecedor_ni IS NOT NULL AND length(fornecedor_ni) = 14
      AND (vigencia_inicio IS NULL OR vigencia_inicio >= current_date - interval '24 months')
  ),
  soc AS (
    SELECT r.ni, lower(trim(s->>'chave')) AS chave, 'socio' AS tipo
    FROM fornecedor_receita r, jsonb_array_elements(r.socios) s
    WHERE r.socios IS NOT NULL AND jsonb_array_length(r.socios) > 0
      AND nullif(trim(s->>'chave'), '') IS NOT NULL
  ),
  ende AS (
    SELECT ni, lower(endereco_chave) AS chave, 'endereco' AS tipo
    FROM fornecedor_receita
    WHERE endereco_chave IS NOT NULL AND char_length(endereco_chave) >= 8
  ),
  atrib AS (SELECT * FROM soc UNION ALL SELECT * FROM ende),
  chave_ok AS (SELECT chave, tipo FROM atrib GROUP BY 1, 2 HAVING count(DISTINCT ni) BETWEEN 2 AND 10),
  vinc AS (
    SELECT a.ni AS ni_a, b.ni AS ni_b, a.tipo
    FROM atrib a
    JOIN chave_ok ck ON ck.chave = a.chave AND ck.tipo = a.tipo
    JOIN atrib b ON b.chave = a.chave AND b.tipo = a.tipo AND b.ni > a.ni
  ),
  pares_orgao AS (
    SELECT fa.orgao_cnpj, v.ni_a, v.ni_b, v.tipo
    FROM vinc v
    JOIN forn_orgao fa ON fa.fornecedor_ni = v.ni_a
    JOIN forn_orgao fb ON fb.fornecedor_ni = v.ni_b AND fb.orgao_cnpj = fa.orgao_cnpj
  ),
  emp AS (
    SELECT orgao_cnpj, count(*) AS n FROM (
      SELECT orgao_cnpj, ni_a AS ni FROM pares_orgao
      UNION
      SELECT orgao_cnpj, ni_b FROM pares_orgao
    ) u GROUP BY 1
  )
  SELECT po.orgao_cnpj AS cnpj,
         count(*)::int AS pares_ligados,
         emp.n AS empresas_envolvidas,
         count(*) FILTER (WHERE po.tipo = 'socio')::int AS pares_socio,
         count(*) FILTER (WHERE po.tipo = 'endereco')::int AS pares_endereco
  FROM pares_orgao po
  JOIN emp ON emp.orgao_cnpj = po.orgao_cnpj
  GROUP BY po.orgao_cnpj, emp.n

Alimenta o painel: Sentinela →

WITH orgao_mun AS (
    SELECT orgao_cnpj, mode() WITHIN GROUP (ORDER BY codigo_ibge) AS cod_ibge
    FROM contratacao
    WHERE codigo_ibge IS NOT NULL AND esfera = 'M'
    GROUP BY orgao_cnpj
  ),
  desp AS (
    SELECT om.cod_ibge,
           sum(d.valor_sancionado) AS valor_sancionado,
           sum(d.economia_potencial) AS sobrepreco_mensuravel
    FROM desperdicio_orgao d
    JOIN orgao_mun om ON om.orgao_cnpj = d.cnpj
    GROUP BY 1
  ),
  conl AS (
    SELECT om.cod_ibge, sum(c.pares_ligados)::int AS conluio_pares
    FROM conluio_orgao c
    JOIN orgao_mun om ON om.orgao_cnpj = c.cnpj
    GROUP BY 1
  ),
  bench AS (
    SELECT cod_ibge, excesso_total
    FROM benchmark_pares
    WHERE exercicio = (SELECT max(exercicio) FROM benchmark_pares)
  ),
  pmvg AS (
    /* revisão adversarial: atribuir via orgao_mun (esfera M), como as demais fontes —
       o join direto por codigo_ibge imputava compras de consórcios/estados/União (44%
       do excedente) ao município da georreferência. Método = o declarado na página. */
    SELECT om.cod_ibge, count(*)::int AS casos_pmvg, sum(p.excedente) AS excedente_pmvg
    FROM compra_acima_pmvg p
    JOIN orgao_mun om ON om.orgao_cnpj = p.orgao_cnpj
    GROUP BY 1
  )
  SELECT m.codigo_ibge AS cod_ibge, m.nome, m.uf, m.populacao,
         ((coalesce(d.valor_sancionado, 0) > 0)::int
          + (coalesce(c.conluio_pares, 0) > 0)::int
          + (coalesce(d.sobrepreco_mensuravel, 0) > 0)::int
          + (coalesce(b.excesso_total, 0) > 0)::int
          + (coalesce(p.casos_pmvg, 0) > 0)::int) AS n_sinais,
         round(coalesce(d.valor_sancionado, 0)::numeric, 2) AS valor_sancionado,
         coalesce(c.conluio_pares, 0) AS conluio_pares,
         round(coalesce(d.sobrepreco_mensuravel, 0)::numeric, 2) AS sobrepreco_mensuravel,
         round(coalesce(b.excesso_total, 0)::numeric, 2) AS excesso_pares,
         coalesce(p.casos_pmvg, 0) AS casos_pmvg,
         round(coalesce(p.excedente_pmvg, 0)::numeric, 2) AS excedente_pmvg
  FROM municipio m
  LEFT JOIN desp d ON d.cod_ibge = m.codigo_ibge
  LEFT JOIN conl c ON c.cod_ibge = m.codigo_ibge
  LEFT JOIN bench b ON b.cod_ibge = m.codigo_ibge
  LEFT JOIN pmvg p ON p.cod_ibge = m.codigo_ibge
  WHERE (coalesce(d.valor_sancionado, 0) > 0 OR coalesce(c.conluio_pares, 0) > 0
         OR coalesce(d.sobrepreco_mensuravel, 0) > 0 OR coalesce(b.excesso_total, 0) > 0
         OR coalesce(p.casos_pmvg, 0) > 0)

Alimenta o painel: Território →