SQL Interview Prep · Aula

Criando uma matriz de retenção

Contando usuários ativos por coorte e período decorrido para formar uma tabela de retenção.

Criando uma matriz de retenção é uma aula grátis de SQL Interview Prep no CoddyKit. Esta é a aula 2 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 que é uma matriz de retenção

A continuação da definição de uma coorte é a famosa matriz de retenção: as linhas são coortes, as colunas são deslocamentos de período (mês 0, 1, 2, ...) e cada célula contabiliza quantos usuários daquela coorte ainda estavam ativos naquele deslocamento.

Os entrevistadores adoram essa questão porque ela exige combinar a atribuição da coorte, uma junção de volta à atividade, um cálculo da diferença entre períodos e uma tabela dinâmica. É a consulta mais representativa da análise de produto.

As duas entradas

Você precisa de duas coisas: o período da coorte de cada usuário, obtido na lição anterior, e um registro de cada período de atividade por usuário. A atividade vem da mesma tabela de eventos, consolidada na granularidade do período.

Portanto, planeje a consulta assim: uma CTE de coorte, depois uma CTE de atividade que liste em quais meses cada usuário esteve ativo e, por fim, faça a junção entre elas.

WITH user_cohort AS (
  SELECT user_id,
    DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events
  GROUP BY user_id
)
SELECT * FROM user_cohort;

Listando os períodos de atividade

A CTE de atividade responde à pergunta "em quais meses cada usuário esteve ativo?" Trunque cada evento para o mês e elimine duplicidades com DISTINCT ou GROUP BY, para que um usuário ativo 40 vezes em março gere uma única linha de março.

Essa lista por usuário e por mês é o que você combina com a coorte para medir a permanência ao longo dos deslocamentos.

WITH activity AS (
  SELECT DISTINCT
    user_id,
    DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT * FROM activity;

Calculando o deslocamento do período

O centro da matriz é o número do período: quantos meses depois do início da coorte ocorreu uma determinada atividade? Subtraia o mês da coorte do mês de atividade.

No Postgres, uma forma clara é contar os meses completos entre as duas datas. Uma fórmula portátil multiplica a diferença de anos por 12 e adiciona a diferença de meses; muitos mecanismos também oferecem funções auxiliares. O deslocamento 0 representa o próprio mês inicial da coorte.

-- months between two month-truncated dates (Postgres)
SELECT
  (EXTRACT(YEAR  FROM active_month) - EXTRACT(YEAR  FROM cohort_month)) * 12
+ (EXTRACT(MONTH FROM active_month) - EXTRACT(MONTH FROM cohort_month))
  AS period_number;

Unindo a Coorte à Atividade

Una a CTE da coorte à CTE da atividade usando user_id. Cada linha de saída significa: este usuário, nascido na coorte X, esteve ativo no deslocamento N. Contar usuários distintos por (coorte, deslocamento) produz a matriz em formato longo.

Como todo membro da coorte está ativo no próprio mês inicial, o deslocamento 0 deve ser igual ao tamanho da coorte, o que fornece uma verificação de consistência integrada.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT c.cohort_month, a.active_month, c.user_id
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id;

A Tabela de Retenção em Formato Longo

Adicione o cálculo do deslocamento e faça a agregação. Agora você tem um resultado organizado em formato longo: uma linha por coorte e por deslocamento, com a contagem de usuários retidos. Muitos entrevistadores aceitam esse resultado diretamente, pois transformar o formato é apenas uma questão visual.

Observe que a expressão do deslocamento aparece tanto em SELECT quanto em GROUP BY, pois é calculada, e não uma coluna armazenada.

WITH user_cohort AS (
  SELECT user_id, DATE_TRUNC('month', MIN(event_at)) AS cohort_month
  FROM events GROUP BY user_id
),
activity AS (
  SELECT DISTINCT user_id, DATE_TRUNC('month', event_at) AS active_month
  FROM events
)
SELECT
  c.cohort_month,
  (EXTRACT(YEAR FROM a.active_month)-EXTRACT(YEAR FROM c.cohort_month))*12
  +(EXTRACT(MONTH FROM a.active_month)-EXTRACT(MONTH FROM c.cohort_month)) AS period_number,
  COUNT(DISTINCT c.user_id) AS retained_users
FROM user_cohort c
JOIN activity a ON a.user_id = c.user_id
GROUP BY c.cohort_month, period_number
ORDER BY c.cohort_month, period_number;

Transformando em Colunas Largas

Para obter a grade clássica, transforme os deslocamentos em colunas usando agregação condicional: um SUM de um CASE para cada deslocamento. Esse padrão portável funciona em qualquer dialeto sem uma sintaxe especial de PIVOT.

Cada CASE emite 1 quando o número do período da linha corresponde àquela coluna, portanto o SUM conta os usuários retidos naquele deslocamento.

SELECT
  cohort_month,
  COUNT(DISTINCT CASE WHEN period_number = 0 THEN user_id END) AS m0,
  COUNT(DISTINCT CASE WHEN period_number = 1 THEN user_id END) AS m1,
  COUNT(DISTINCT CASE WHEN period_number = 2 THEN user_id END) AS m2,
  COUNT(DISTINCT CASE WHEN period_number = 3 THEN user_id END) AS m3
FROM retention_long
GROUP BY cohort_month
ORDER BY cohort_month;

De Contagens a Taxas de Retenção

Os entrevistadores geralmente querem porcentagens, não contagens brutas. Divida os usuários retidos em cada deslocamento pelo tamanho da coorte (deslocamento 0). Use CAST para um número de ponto flutuante ou multiplique por 1.0 para evitar a divisão inteira, o erro silencioso mais comum neste caso.

O resultado é uma curva de retenção: 100% no mês 0, caindo em direção a um platô. Esse platô é a métrica que realmente importa para as partes interessadas.

SELECT
  cohort_month,
  period_number,
  retained_users,
  ROUND(
    100.0 * retained_users
    / MAX(retained_users) OVER (PARTITION BY cohort_month),
    1
  ) AS retention_pct
FROM retention_long
ORDER BY cohort_month, period_number;

A Armadilha da Divisão Inteira

Uma pegadinha garantida em entrevistas: na maioria dos mecanismos, 120 / 500 é igual a 0, e não a 0.24, porque os dois operandos são inteiros. As porcentagens de retenção acabam sendo silenciosamente exibidas como zero.

Corrija isso tornando um dos lados numérico: multiplique por 100.0, faça CAST de um operando para NUMERIC ou divida por NULLIF(size, 0) para também se proteger contra uma coorte vazia. Dizer que "e NULLIF evita a divisão por zero" garante pontos extras.

SELECT
  retained_users,
  cohort_size,
  100.0 * retained_users / NULLIF(cohort_size, 0) AS pct
FROM retention_long;

Preenchendo Deslocamentos Ausentes com Zero

Se uma coorte teve zero usuários retidos no deslocamento 2, o JOIN não produz nenhuma linha, deixando um espaço vazio na matriz. Para exibir um 0 explícito, gere a grade completa de combinações (coorte, deslocamento) e faça um LEFT JOIN das contagens.

Monte a grade fazendo um CROSS JOIN entre as coortes e uma lista de números/deslocamentos; depois, transforme as contagens ausentes em zero. Os entrevistadores valorizam quando você percebe essa lacuna.

WITH offsets AS (SELECT generate_series(0, 6) AS period_number),
cohorts AS (SELECT DISTINCT cohort_month FROM retention_long)
SELECT
  c.cohort_month, o.period_number,
  COALESCE(r.retained_users, 0) AS retained_users
FROM cohorts c
CROSS JOIN offsets o
LEFT JOIN retention_long r
  ON r.cohort_month = c.cohort_month
 AND r.period_number = o.period_number
ORDER BY c.cohort_month, o.period_number;

Formato Triangular e Viés de Recência

Mais um ponto para abordar: a matriz é triangular. Uma coorte que começou no mês passado ainda não pode ter um valor para o mês 3, portanto os deslocamentos posteriores têm menos coortes contribuindo.

Por isso, comparar a média de uma coluna entre coortes é tendencioso em favor das coortes mais antigas. Mencione que você mostraria o triângulo honestamente ou restringiria as comparações aos deslocamentos que todas as coortes já alcançaram. Essa percepção diferencia analistas de autores de consultas.

Verificação Rápida

Sua consulta de retenção divide os usuários retidos pelo tamanho da coorte, mas todas as porcentagens são exibidas como 0, exceto no mês 0. Qual é a causa mais provável?

Recapitulação: a Matriz de Retenção

Para criar uma matriz de retenção em uma entrevista:

Atribua a cada usuário um período da coorte e depois liste os períodos de atividade de cada usuário, eliminando duplicatas.
Una os dois conjuntos e calcule o deslocamento do período (meses entre a coorte e a atividade).
Faça a agregação em formato longo com COUNT(DISTINCT user_id); use CASE para transformar o resultado em uma grade, se necessário.
Converta as contagens em taxas com cuidado, evitando a divisão inteira e a divisão por zero com 100.0 e NULLIF.
Faça um LEFT JOIN de uma grade gerada para preencher as células com zero e lembre-se de que a matriz é triangular.

Perguntas Frequentes

A aula “Criando uma matriz de retenção” é grátis?

Sim — o texto completo de “Criando uma matriz de retenção” é 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 “Criando uma matriz de retenção”?

Contando usuários ativos por coorte e período decorrido para formar uma tabela de retenção. 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 2 de 4.

Quanto tempo leva a aula “Criando uma matriz de retenção”?

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