SQL para RevOps: seis construções resolvem noventa por cento das perguntas

Quais negócios abertos já passaram de dois ciclos medianos do próprio segmento e portanto deveriam estar fechados como perdidos?

-- Grão da saída: uma linha por oportunidade aberta. Nenhum JOIN, nenhum risco.
-- Os limites vêm do ciclo mediano de cada segmento, não de um número redondo.
SELECT  oportunidade_id,
        conta_id,
        segmento,
        acv,
        data_criacao,
        CURRENT_DATE - data_criacao AS dias_aberto
FROM    fato_oportunidade
WHERE   resultado = 'aberta'
  AND   CURRENT_DATE - data_criacao > CASE segmento
                                        WHEN 'smb' THEN 76
                                        WHEN 'mid' THEN 188
                                        WHEN 'enterprise' THEN 372
                                      END
ORDER BY dias_aberto DESC;

Qual o win rate por contagem e por valor de cada segmento, considerando apenas negócios que já tiveram desfecho?

-- Grão da saída: uma linha por segmento. Nenhum JOIN.
-- WHERE fora do GROUP BY: "resolvidas" é o denominador declarado desta métrica.
SELECT  segmento,
        COUNT(*)                                        AS resolvidas,
        COUNT(*) FILTER (WHERE resultado = 'ganha')     AS ganhas,
        ROUND(100.0 * COUNT(*) FILTER (WHERE resultado = 'ganha')
                    / COUNT(*), 1)                      AS wr_contagem_pct,
        SUM(acv)                                        AS valor_resolvido,
        SUM(acv) FILTER (WHERE resultado = 'ganha')     AS valor_ganho,
        ROUND(100.0 * SUM(acv) FILTER (WHERE resultado = 'ganha')
                    / SUM(acv), 1)                      AS wr_valor_pct
FROM    fato_oportunidade
WHERE   resultado IN ('ganha', 'perdida')
GROUP BY segmento
ORDER BY segmento;

Quanto de receita a empresa ganhou nos negócios fechados, quando os detalhes de contrato estão numa segunda tabela?

-- ERRADO. Grão de entrada: uma linha por oportunidade.
-- Grão da saída depois do JOIN: uma linha por ITEM. Ninguém declarou, ninguém viu.
SELECT SUM(o.acv) AS receita_ganha
FROM   fato_oportunidade o
JOIN   fato_contrato_item i ON i.oportunidade_id = o.oportunidade_id
WHERE  o.resultado = 'ganha';
-- devolve 331.200

-- CERTO, jeito 1: some a coluna do grão em que você realmente está.
SELECT SUM(i.valor_item) AS receita_ganha
FROM   fato_oportunidade o
JOIN   fato_contrato_item i ON i.oportunidade_id = o.oportunidade_id
WHERE  o.resultado = 'ganha';
-- devolve 145.200

-- CERTO, jeito 2: agregue ANTES de juntar, e o grão da saída volta a ser a oportunidade.
WITH itens_por_negocio AS (
  SELECT oportunidade_id, SUM(valor_item) AS valor_itens
  FROM   fato_contrato_item
  GROUP BY oportunidade_id
)
SELECT SUM(o.acv) AS receita_ganha
FROM   fato_oportunidade o
JOIN   itens_por_negocio t ON t.oportunidade_id = o.oportunidade_id
WHERE  o.resultado = 'ganha';
-- devolve 145.200

-- O TESTE. Rode antes e depois de qualquer JOIN. Se mudou e não deveria, pare.
SELECT COUNT(*) FROM fato_oportunidade WHERE resultado = 'ganha';          -- 3
SELECT COUNT(*) FROM fato_oportunidade o
JOIN fato_contrato_item i ON i.oportunidade_id = o.oportunidade_id
WHERE o.resultado = 'ganha';                                              -- 6

Qual o ciclo mediano e o percentil 90 de cada segmento, medidos separadamente sobre ganhos e sobre perdas?

-- Grão da saída: uma linha por combinação de segmento e resultado.
WITH resolvidas AS (
  SELECT  oportunidade_id,
          segmento,
          resultado,
          data_fechamento_real - data_criacao AS ciclo_dias
  FROM    fato_oportunidade
  WHERE   resultado IN ('ganha', 'perdida')
)
SELECT  segmento,
        resultado,
        COUNT(*)                                                  AS negocios,
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY ciclo_dias)   AS mediana_dias,
        PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY ciclo_dias)   AS p90_dias,
        ROUND(AVG(ciclo_dias), 1)                                 AS media_dias
FROM    resolvidas
GROUP BY segmento, resultado
ORDER BY segmento, resultado;

O ARR novo de cada mês de 2025 cresceu em relação ao mês anterior, e quanto já está acumulado até cada ponto do ano? E quantas contas distintas a empresa realmente trabalhou, e não quantos negócios abriu?

-- Grão da saída: uma linha por mês. LAG olha para trás, SUM OVER acumula.
SELECT  mes,
        acv_novo,
        LAG(acv_novo) OVER (ORDER BY mes)                      AS mes_anterior,
        acv_novo - LAG(acv_novo) OVER (ORDER BY mes)           AS variacao,
        ROUND(100.0 * (acv_novo::numeric
              / LAG(acv_novo) OVER (ORDER BY mes) - 1), 1)     AS variacao_pct,
        SUM(acv_novo) OVER (ORDER BY mes
              ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS acumulado
FROM    arr_novo_mensal
ORDER BY mes;

-- Mesma família, outro problema: a primeira oportunidade de cada conta.
-- Grão da saída: uma linha por conta. ROW_NUMBER numera dentro de cada partição.
WITH ordenadas AS (
  SELECT  oportunidade_id,
          conta_id,
          data_criacao,
          resultado,
          ROW_NUMBER() OVER (PARTITION BY conta_id ORDER BY data_criacao) AS ordem
  FROM    fato_oportunidade
)
SELECT  COUNT(*) AS contas_trabalhadas
FROM    ordenadas
WHERE   ordem = 1;
-- devolve 10, contra 12 linhas na tabela de origem

Qual a conversão acumulada até o ganho de cada safra mensal de criação, e quais safras ainda não podem ser lidas?

-- Grão da saída: uma linha por safra mensal de criação.
-- A data-âncora é data_criacao. Se fosse data_fechamento_real, a pergunta seria outra.
WITH safras AS (
  SELECT  DATE_TRUNC('month', data_criacao) AS safra,
          resultado
  FROM    fato_oportunidade
)
SELECT  safra,
        COUNT(*)                                            AS criadas,
        COUNT(*) FILTER (WHERE resultado <> 'aberta')       AS resolvidas,
        COUNT(*) FILTER (WHERE resultado = 'aberta')        AS ainda_abertas,
        COUNT(*) FILTER (WHERE resultado = 'ganha')         AS ganhas,
        ROUND(100.0 * COUNT(*) FILTER (WHERE resultado = 'ganha')
              / NULLIF(COUNT(*) FILTER (WHERE resultado <> 'aberta'), 0), 1)
                                                            AS conversao_acumulada_pct
FROM    safras
GROUP BY safra
ORDER BY safra;

-- Para a tabela triangular de retenção, o mês de vida sai da mesma ideia:
--   DATE_PART('month', AGE(mes_da_cobranca, mes_de_entrada))
--   + 12 * DATE_PART('year', AGE(mes_da_cobranca, mes_de_entrada))  AS mes_de_vida
-- Em BigQuery: DATE_DIFF(mes_da_cobranca, mes_de_entrada, MONTH).