SQL Interview Prep · Aula

Consultas de cancelamento e retorno

Identificando usuários que saíram e os que retornaram após um intervalo.

Aula 4 de 413 etapas

Consultas de cancelamento e retorno é uma aula grátis de SQL Interview Prep no CoddyKit. Esta é a aula 4 de 4. Você pode ler a aula completa abaixo gratuitamente — depois pratica ao vivo no navegador com um editor de código integrado e um tutor de IA 24/7. Faz parte do caminho de aprendizado de SQL Interview Prep, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de SQL Interview Prep inclui 4 aulas no total.

O Outro Lado da Retenção

Se a retenção mede quem ficou, a evasão mede quem saiu, e a reativação mede quem voltou. Os entrevistadores combinam essas métricas com a retenção porque elas revelam se você consegue raciocinar sobre a ausência de atividade, o que é mais difícil do que contar presenças.

O truque recorrente é: você não pode filtrar linhas que não existem. As consultas de evasão tratam fundamentalmente de encontrar a lacuna entre a última atividade de um usuário e o momento atual (ou a próxima atividade).

Definindo a Evasão com Precisão

"Abandono" não significa nada sem uma janela. Uma definição comum é: um usuário está em evasão se não teve nenhuma atividade nos últimos 30 dias. O limite de 30 dias de inatividade é uma decisão da empresa que você precisa estabelecer com precisão.

Para produtos por assinatura, evasão pode significar, em vez disso, uma assinatura cancelada ou expirada: uma mudança de estado, e não uma lacuna de atividade. Esclareça qual modelo se aplica antes de escrever SQL.

Última Atividade por Usuário

A base da evasão por lacuna de atividade é o evento mais recente de cada usuário. Agrupe por usuário e obtenha o MAX da data do evento.

Esse único valor, comparado com a data de hoje, informa há quanto tempo o usuário está em silêncio. Todo o processamento seguinte é uma comparação com essa data da última atividade.

SELECT
  user_id,
  MAX(event_at::date) AS last_active
FROM events
GROUP BY user_id;

A Consulta de Usuários em Evasão

Um usuário está em evasão se sua última atividade ocorreu há mais de 30 dias. Compare last_active com CURRENT_DATE - 30. Qualquer pessoa cujo evento mais recente seja anterior a esse limite ficou inativa.

Observe que o trabalho acontece depois da agregação: você reduz os dados a uma linha por usuário e então testa a lacuna. Filtrar os eventos brutos por data diria apenas quem esteve inativo em uma janela, não quem está em evasão de modo geral.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events
  GROUP BY user_id
)
SELECT user_id, last_active
FROM last_seen
WHERE last_active < CURRENT_DATE - INTERVAL '30 days';

Calculando a Taxa de Evasão

A taxa de evasão é o número de usuários em evasão dividido pela base relevante, geralmente os usuários que estavam ativos no início do período. Use agregação condicional para contar os usuários em evasão e o total em uma única passagem; depois, divida cuidadosamente usando 100.0 e NULLIF.

Seja explícito sobre o denominador na entrevista: evasão entre todos os usuários históricos e evasão entre usuários anteriormente ativos são métricas diferentes.

WITH last_seen AS (
  SELECT user_id, MAX(event_at::date) AS last_active
  FROM events GROUP BY user_id
)
SELECT
  COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days'
  ) AS churned,
  COUNT(*) AS total_users,
  ROUND(100.0 * COUNT(*) FILTER (
    WHERE last_active < CURRENT_DATE - INTERVAL '30 days')
    / NULLIF(COUNT(*), 0), 1) AS churn_pct
FROM last_seen;

Evasão Período a Período com Lógica de Conjuntos

Outra forma de abordar a questão é: quem estava ativo no mês passado, mas não neste mês? Essa é uma diferença entre conjuntos. Crie o conjunto de usuários ativos no mês passado e o conjunto de usuários ativos neste mês; depois, encontre os membros do primeiro que não estão no segundo.

Você pode expressar isso com EXCEPT, com um anti-join usando LEFT JOIN / IS NULL ou com NOT EXISTS. O anti-join é a opção mais portável e a que os entrevistadores mais costumam querer ver.

WITH last_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-04-01' AND event_at < DATE '2024-05-01'
),
this_month AS (
  SELECT DISTINCT user_id FROM events
  WHERE event_at >= DATE '2024-05-01' AND event_at < DATE '2024-06-01'
)
SELECT user_id FROM last_month
EXCEPT
SELECT user_id FROM this_month;

A forma de junção anti

A mesma consulta de abandono deste período na forma de uma junção anti: faça um LEFT JOIN dos usuários ativos deste mês aos do mês passado e mantenha as linhas em que a correspondência seja NULL. Esses são os usuários presentes no mês passado, mas ausentes neste mês: os que abandonaram.

NOT EXISTS é uma resposta igualmente boa e trata NULL com segurança. Mencione que NOT IN seria arriscado se o conjunto interno pudesse conter NULL, uma armadilha clássica.

SELECT lm.user_id
FROM last_month lm
LEFT JOIN this_month tm ON tm.user_id = lm.user_id
WHERE tm.user_id IS NULL;

Definindo a reativação

Reativação (também chamada de ressurreição) é o caso de um usuário que havia abandonado o produto e depois voltou a ficar ativo. O padrão é uma lacuna na linha do tempo: ativo, depois um período de silêncio mais longo que o limite de abandono e, por fim, ativo novamente.

Portanto, um usuário reativado neste mês é aquele que está ativo agora, estava inativo no período anterior, mas teve atividade em algum período mais antigo. É a imagem espelhada do abandono.

Detectando lacunas com LAG

A maneira elegante de encontrar reativações é usar a função de janela LAG: para cada período de atividade de cada usuário, consulte o período ativo anterior. Se a lacuna entre eles exceder o limite, este período será uma reativação.

LAG evita uma junção da tabela consigo mesma e torna a consulta mais clara. Particione por usuário, ordene pelo período ativo e compare cada período com o seu predecessor.

WITH monthly AS (
  SELECT DISTINCT user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
),
gaps AS (
  SELECT user_id, active_month,
    LAG(active_month) OVER (
      PARTITION BY user_id ORDER BY active_month
    ) AS prev_month
  FROM monthly
)
SELECT user_id, active_month AS resurrected_month
FROM gaps
WHERE prev_month IS NOT NULL
  AND active_month > prev_month + INTERVAL '1 month';

Novos, reativados e retidos

Uma consulta completa de classificação de atividade rotula cada usuário ativo neste período como: novo (sem atividade anterior), retido (também ativo no período anterior) ou reativado (com atividade anterior, mas com uma lacuna). O prev_month de LAG determina os três casos.

prev_month IS NULL → novo
prev_month = active_month - 1 → retido
caso contrário (uma lacuna) → reativado

Produzir essa classificação é uma resposta forte e completa.

SELECT user_id, active_month,
  CASE
    WHEN prev_month IS NULL THEN 'new'
    WHEN active_month = prev_month + INTERVAL '1 month' THEN 'retained'
    ELSE 'resurrected'
  END AS user_state
FROM gaps;

A armadilha de NOT IN com NULL

Uma última armadilha. Se você escrever o abandono como WHERE user_id NOT IN (SELECT user_id FROM this_month) e essa subconsulta retornar pelo menos um NULL, o resultado inteiro ficará vazio, porque NOT IN será avaliado como UNKNOWN diante de NULL.

Prefira NOT EXISTS ou uma junção anti com LEFT JOIN / IS NULL, que funcionam corretamente com NULL. Mencionar essa diferença sem que ninguém peça é um sinal confiável de experiência sênior em entrevistas de retenção.

-- safe anti-join instead of NOT IN
SELECT lm.user_id
FROM last_month lm
WHERE NOT EXISTS (
  SELECT 1 FROM this_month tm
  WHERE tm.user_id = lm.user_id
);

Verificação rápida

Você quer encontrar os usuários ativos no mês passado, mas não neste mês. Um colega escreveu WHERE user_id NOT IN (SELECT user_id FROM this_month), e a consulta retorna zero linhas, embora alguns usuários claramente tenham abandonado. Qual é a correção mais segura?

Responda na aula interativa

Resumo: abandono e reativação

O essencial sobre abandono e reativação:

Defina o abandono por um limite de inatividade (por exemplo, nenhuma atividade durante 30 dias) ou por uma mudança no status da assinatura — esclareça qual dos dois.
Calcule o MAX(última atividade) de cada usuário e compare-o com CURRENT_DATE - threshold.
O abandono entre períodos é uma diferença de conjuntos: use EXCEPT, NOT EXISTS ou uma junção anti com LEFT JOIN / IS NULL.
A reativação é uma lacuna na linha do tempo; detecte-a com LAG para classificar os usuários como novos, retidos ou reativados.
Evite NOT IN quando NULL for possível — isso esvazia o resultado silenciosamente.

Grátis para começar

Aprenda SQL com um tutor de IA — grátis

Escreva e execute código real no seu navegador, obtenha ajuda instantânea de um tutor de IA 24/7 e continue de onde parou na web ou no app.

Cursos: 30
Aulas: 120

Começar a aprender grátis Ver o curso completo

Perguntas Frequentes

A aula “Consultas de cancelamento e retorno” é grátis?

Sim — o texto completo de “Consultas de cancelamento e retorno” é grátis para ler aqui na web. Para praticá-la interativamente (um editor de código integrado e um tutor de IA 24/7) e desbloquear o restante do curso de SQL Interview Prep, atualize para CoddyKit PRO. O curso de SQL Interview Prep inclui 4 aulas no total.

O que vou aprender em “Consultas de cancelamento e retorno”?

Identificando usuários que saíram e os que retornaram após um intervalo. Você pratica SQL Interview Prep com código prático que executa diretamente no navegador, e um tutor de IA 24/7 responde suas dúvidas enquanto trabalha na aula.

Preciso ter experiência prévia para começar SQL Interview Prep?

Nenhuma experiência prévia é necessária. SQL Interview Prep no CoddyKit é estruturado para alunos iniciantes até avançados, então você pode começar aqui ou desde o início e aprender no seu ritmo. Esta é a aula 4 de 4.

Quanto tempo leva a aula “Consultas de cancelamento e retorno”?

A maioria das aulas CoddyKit leva cerca de 5–10 minutos. Cada uma é compacta e interativa, então você faz progresso constante e retoma exatamente de onde parou entre web e app.

Posso escrever e executar código nesta aula de SQL Interview Prep?

Sim. Cada aula de SQL Interview Prep inclui um editor de código integrado, então você escreve e executa código real direto no navegador e recebe feedback de IA instantaneamente — nenhuma configuração local necessária.

Todas as aulas deste curso

← Voltar para SQL Interview Prep