data-write-query — Escrever SQL Otimizado

Escreve uma query SQL a partir de uma descrição em linguagem natural, otimizada para o dialeto PostgreSQL (stack padrão Evolution) e seguindo boas práticas.

Uso

/data-write-query <descrição do que você precisa consultar>

Fluxo de Trabalho

1. Entender a Requisição

Analisar a descrição do usuário para identificar:

• Colunas de saída: Quais campos devem estar no resultado?
• Filtros: Quais condições limitam os dados (intervalos de tempo, segmentos, status)?
• Agregações: Há operações GROUP BY, contagens, somas, médias?
• Joins: É necessário combinar múltiplas tabelas?
• Ordenação: Como os resultados devem ser classificados?
• Limites: Há um requisito de top-N ou amostragem?

2. Determinar o Dialeto SQL

Dialeto primário do workspace:

• PostgreSQL — padrão para todo o stack Evolution (Evo CRM, Evo AI, serviços internos, Aurora RDS, Supabase, Neon)

Dialetos secundários (se explicitamente solicitados):

• Snowflake
• BigQuery (Google Cloud)
• Redshift (Amazon)
• Databricks SQL
• MySQL / Aurora MySQL
• DuckDB
• SQLite

Se o usuário não especificar, assumir PostgreSQL como padrão.

3. Descobrir o Schema (Se Conectado)

Se a fonte de dados estiver disponível via MCP ou CLI:

1. Buscar tabelas relevantes com base na descrição do usuário
2. Inspecionar nomes de colunas, tipos e relacionamentos
3. Verificar índices, particionamento ou clustering que afetam a performance
4. Procurar views ou views materializadas que possam simplificar a query

Queries de exploração de schema (PostgreSQL):

-- Listar todas as tabelas no schema
SELECT table_name, table_type
FROM information_schema.tables
WHERE table_schema = 'public'
ORDER BY table_name;

-- Detalhes das colunas de uma tabela
SELECT column_name, data_type, is_nullable, column_default
FROM information_schema.columns
WHERE table_name = 'nome_da_tabela'
ORDER BY ordinal_position;

-- Tamanho das tabelas
SELECT relname AS tabela,
       pg_size_pretty(pg_total_relation_size(relid)) AS tamanho_total
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

-- Índices de uma tabela
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'nome_da_tabela';

4. Escrever a Query

Seguir estas boas práticas:

Estrutura:

• Usar CTEs (cláusulas WITH) para legibilidade quando queries têm múltiplos passos lógicos
• Uma CTE por transformação lógica ou fonte de dados
• Nomear CTEs de forma descritiva (ex: novos_clientes_diarios, usuarios_ativos, receita_por_plano)

Performance:

• Nunca usar SELECT * em queries de produção — especificar apenas as colunas necessárias
• Filtrar cedo (push de cláusulas WHERE o mais próximo possível das tabelas base)
• Usar filtros de partição quando disponíveis (especialmente partições de data)
• Preferir EXISTS sobre IN para subconsultas com grandes conjuntos de resultados
• Usar os tipos corretos de JOIN (não usar LEFT JOIN quando INNER JOIN é o correto)
• Evitar subconsultas correlacionadas quando um JOIN ou window function funciona
• Atenção a joins explosivos (many-to-many)

Legibilidade:

• Adicionar comentários explicando o "porquê" para lógica não óbvia
• Usar indentação e formatação consistentes
• Criar aliases de tabelas com nomes abreviados significativos (não apenas a, b, c)
• Colocar cada cláusula principal em sua própria linha

Otimizações específicas do PostgreSQL:

-- Usar EXPLAIN ANALYZE para entender o plano de execução
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT ...;

-- Usar LIMIT + OFFSET para paginação
SELECT * FROM tabela
ORDER BY criado_em DESC
LIMIT 50 OFFSET 0;

-- Window functions eficientes no PostgreSQL
SELECT
    cliente_id,
    valor,
    SUM(valor) OVER (PARTITION BY cliente_id ORDER BY data_pagamento) AS valor_acumulado,
    ROW_NUMBER() OVER (PARTITION BY cliente_id ORDER BY data_pagamento DESC) AS rn
FROM pagamentos;

-- DISTINCT ON (específico do PostgreSQL) — mais eficiente que ROW_NUMBER
SELECT DISTINCT ON (cliente_id)
    cliente_id, plano, criado_em
FROM assinaturas
ORDER BY cliente_id, criado_em DESC;

-- Funções de data no PostgreSQL
DATE_TRUNC('month', criado_em)           -- Início do mês
EXTRACT(DOW FROM criado_em)              -- Dia da semana (0=domingo)
NOW() AT TIME ZONE 'America/Sao_Paulo'  -- Hora atual em BRT
criado_em AT TIME ZONE 'UTC' AT TIME ZONE 'America/Sao_Paulo' -- Converter para BRT
INTERVAL '30 days'                       -- Subtração de intervalo
DATE_PART('epoch', fim - inicio)        -- Diferença em segundos

-- JSON/JSONB (comum no stack Evolution)
dados->>'campo'                          -- Extrair como texto
dados->'campo'                           -- Extrair como JSON
jsonb_array_elements(dados->'lista')    -- Expandir array JSON
dados @> '{"status": "active"}'::jsonb  -- Contém (usa índice GIN)

-- Array operations
ANY(ARRAY['active', 'trial'])           -- In array
array_agg(campo ORDER BY data)          -- Agregar em array
unnest(tags)                            -- Expandir array em linhas

Padrões de query para as fontes do workspace:

-- Padrão: Análise de MRR mensal (típico para dados exportados do Stripe)
WITH receita_mensal AS (
    SELECT
        DATE_TRUNC('month', data_pagamento) AS mes,
        SUM(valor_cents) / 100.0 AS receita_total,
        COUNT(DISTINCT cliente_id) AS clientes_pagantes
    FROM pagamentos
    WHERE status = 'succeeded'
      AND data_pagamento >= NOW() - INTERVAL '12 months'
    GROUP BY 1
),
crescimento AS (
    SELECT
        mes,
        receita_total,
        clientes_pagantes,
        LAG(receita_total) OVER (ORDER BY mes) AS receita_anterior,
        ROUND(
            (receita_total - LAG(receita_total) OVER (ORDER BY mes)) /
            NULLIF(LAG(receita_total) OVER (ORDER BY mes), 0) * 100, 2
        ) AS crescimento_pct
    FROM receita_mensal
)
SELECT * FROM crescimento ORDER BY mes;

-- Padrão: Instâncias ativas com versão (típico para Licensing)
WITH instancias_ativas AS (
    SELECT
        instancia_id,
        versao,
        pais,
        criado_em,
        ultimo_ping
    FROM instancias
    WHERE ultimo_ping >= NOW() - INTERVAL '24 hours'
      AND status = 'active'
),
por_versao AS (
    SELECT
        versao,
        COUNT(*) AS total,
        COUNT(DISTINCT pais) AS paises_distintos
    FROM instancias_ativas
    GROUP BY 1
)
SELECT
    versao,
    total,
    paises_distintos,
    ROUND(total * 100.0 / SUM(total) OVER (), 2) AS percentual
FROM por_versao
ORDER BY total DESC;

-- Padrão: Análise de funil (Evo CRM)
WITH funil AS (
    SELECT
        DATE_TRUNC('week', criado_em) AS semana,
        COUNT(*) FILTER (WHERE etapa = 'lead') AS leads,
        COUNT(*) FILTER (WHERE etapa = 'qualificado') AS qualificados,
        COUNT(*) FILTER (WHERE etapa = 'proposta') AS propostas,
        COUNT(*) FILTER (WHERE etapa = 'fechado_ganho') AS fechados
    FROM oportunidades
    WHERE criado_em >= NOW() - INTERVAL '90 days'
    GROUP BY 1
)
SELECT
    semana,
    leads,
    qualificados,
    propostas,
    fechados,
    ROUND(qualificados * 100.0 / NULLIF(leads, 0), 1) AS taxa_qualificacao,
    ROUND(fechados * 100.0 / NULLIF(leads, 0), 1) AS taxa_fechamento
FROM funil
ORDER BY semana;

-- Padrão: Análise de cohort de retenção
WITH cohorts AS (
    SELECT
        cliente_id,
        DATE_TRUNC('month', primeira_assinatura) AS cohort_mes
    FROM clientes
),
atividade_mensal AS (
    SELECT DISTINCT
        cliente_id,
        DATE_TRUNC('month', data_evento) AS mes_ativo
    FROM eventos
),
retencao AS (
    SELECT
        c.cohort_mes,
        DATE_PART('month', AGE(a.mes_ativo, c.cohort_mes)) AS meses_depois,
        COUNT(DISTINCT c.cliente_id) AS clientes_retidos
    FROM cohorts c
    JOIN atividade_mensal a USING (cliente_id)
    WHERE a.mes_ativo >= c.cohort_mes
    GROUP BY 1, 2
),
tamanho_cohort AS (
    SELECT cohort_mes, COUNT(DISTINCT cliente_id) AS tamanho
    FROM cohorts
    GROUP BY 1
)
SELECT
    r.cohort_mes,
    r.meses_depois,
    r.clientes_retidos,
    tc.tamanho AS tamanho_cohort,
    ROUND(r.clientes_retidos * 100.0 / tc.tamanho, 1) AS taxa_retencao
FROM retencao r
JOIN tamanho_cohort tc USING (cohort_mes)
ORDER BY r.cohort_mes, r.meses_depois;

5. Apresentar a Query

Fornecer:

1. A query completa em bloco de código SQL com syntax highlighting
2. Breve explicação do que cada CTE ou seção faz
3. Notas de performance se relevantes (uso de índice, bottlenecks potenciais, custo esperado)
4. Sugestões de modificação — como ajustar para variações comuns (intervalo de tempo diferente, granularidade diferente, filtros adicionais)

6. Oferecer Execução

Se a fonte de dados estiver disponível via skill, oferecer executar a query e analisar os resultados. Se o usuário preferir executar manualmente, a query está pronta para copiar e colar.

Exemplos

Agregação simples:

/data-write-query Contagem de assinaturas por plano nos últimos 30 dias

Análise complexa:

/data-write-query Análise de retenção por cohort — agrupar clientes pelo mês de assinatura, depois mostrar qual percentual ainda está ativo aos 1, 3, 6 e 12 meses

Performance crítica:

/data-write-query Temos uma tabela de eventos com 500M de linhas particionada por data. Encontrar os 100 usuários com mais eventos nos últimos 7 dias com o tipo de evento mais recente de cada um.

Com timezone:

/data-write-query Instâncias criadas esta semana, agrupadas por dia em BRT (America/Sao_Paulo)

Dicas

• Mencionar o dialeto SQL upfront para obter a sintaxe correta imediatamente (PostgreSQL é o padrão)
• Se souber os nomes das tabelas, inclua-os — caso contrário, ajudaremos a encontrá-los
• Especificar se a query precisa ser idempotente (segura para re-executar) ou é para uso único
• Para queries recorrentes, mencionar se deve ser parametrizada para intervalos de datas
• Sempre especificar o fuso horário: o stack Evolution usa UTC no banco, BRT (America/Sao_Paulo) na exibição
• Mencionar se precisa de EXPLAIN ANALYZE para diagnóstico de performance