0Pricing
SQL Interview Prep · Aula

Maior sequência por usuário

Calculando o comprimento máximo de uma sequência consecutiva dentro de cada grupo.

Maior sequência por usuário é 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.

A pergunta

Uma pergunta complementar frequente à detecção de dias consecutivos: "Para cada usuário, qual é sua sequência mais longa de dias consecutivos de atividade?" As equipes de produto e crescimento fazem essa pergunta constantemente para medir o engajamento.

Você já sabe identificar cada sequência. A nova etapa é encontrar o comprimento máximo por usuário e, muitas vezes, retornar também as datas dessa melhor sequência. Esta lição se baseia diretamente no esqueleto de lacunas e ilhas.

Relembrando o construtor de ilhas

Na lição anterior, o agrupamento por sequência usa login_date - ROW_NUMBER() como âncora da ilha. Cada usuário pode ter várias ilhas; primeiro calcularemos uma linha por ilha e depois reduziremos tudo a uma linha por usuário.

Mantenha este plano de duas camadas em mente: primeiro construa as ilhas, depois agregue as ilhas.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT user_id, login_date - rn AS grp
FROM numbered;

Uma linha por ilha

Consolide cada ilha em uma única linha de resumo, contendo seu comprimento e intervalo de datas. Agrupe por usuário e pela âncora, e calcule as métricas.

Damos a essa CTE o nome islands para que a camada seguinte possa consultá-la com clareza.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
)
SELECT * FROM islands;

Resposta simples: comprimento máximo com MAX

Se o entrevistador quiser apenas o comprimento, a etapa final é uma única linha: agrupe as ilhas por usuário e obtenha o comprimento máximo.

Essa é a resposta mais clara quando as datas de início e fim não são necessárias.

-- ...numbered and islands CTEs as before...
SELECT
  user_id,
  MAX(streak_len) AS longest_streak
FROM islands
GROUP BY user_id
ORDER BY user_id;

Retornando também as datas

Muitas vezes, o entrevistador acrescenta: "e mostre quando essa sequência ocorreu". Um MAX simples não informa qual ilha venceu. Você precisa classificar as ilhas dentro de cada usuário e manter a classificação 1.

Use ROW_NUMBER ordenado pelo comprimento em ordem decrescente, para que a melhor sequência de cada usuário receba a classificação 1. Adicione um critério de desempate para resolver empates de forma determinística.

ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC, streak_start ASC
) AS rnk

Classificando e filtrando

Envolva a classificação em uma CTE e depois filtre por rnk = 1. Não é possível filtrar diretamente uma função de janela em WHERE, portanto a camada extra é obrigatória.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1;

RANK versus ROW_NUMBER em caso de empate

E se um usuário tiver duas sequências de mesmo comprimento máximo e o entrevistador quiser que ambas sejam retornadas? Troque ROW_NUMBER por RANK e mantenha rnk = 1.

  • ROW_NUMBER — exatamente uma vencedora por usuário (a escolha é arbitrária em caso de empate, a menos que você adicione um critério de desempate).
  • RANK — todas as sequências mais longas empatadas compartilham a classificação 1 e são mantidas.

Esclareça qual comportamento é desejado; isso demonstra atenção aos casos extremos.

RANK() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC
) AS rnk  -- keep all rnk = 1

Exemplo resolvido

Suponha que o usuário 7 tenha feito acessos de 1 a 4 de janeiro, depois de 10 a 11, e depois de 20 a 23. São três ilhas, com comprimentos 4, 2 e 4. O maior comprimento é 4, e há um empate.

  • Com ROW_NUMBER + critério de desempate streak_start: retorna apenas a sequência de 1 a 4 de janeiro.
  • Com RANK: retorna as sequências de 1 a 4 e de 20 a 23 de janeiro.

Explicar isso em voz alta demonstra que você raciocinou sobre as duplicatas.

Como lidar com usuários sem acessos

Um entrevistador pode perguntar: "E quanto aos usuários que nunca acessaram?" Esses usuários não têm linhas em logins, então desaparecem do resultado. Se eles precisarem aparecer com uma sequência de 0, faça um LEFT JOIN com a tabela completa de users e use COALESCE.

SELECT u.user_id,
  COALESCE(MAX(i.streak_len), 0) AS longest_streak
FROM users u
LEFT JOIN islands i ON i.user_id = u.user_id
GROUP BY u.user_id;

Observações de desempenho

Esse padrão faz uma única passagem ordenada pelos dados, além de um agrupamento. Para mantê-lo rápido:

  • Garanta um índice em (user_id, login_date) para que o ORDER BY da janela evite uma ordenação.
  • Remova as duplicatas antecipadamente se a origem tiver vários eventos por dia.
  • Evite envolver login_date em funções no ORDER BY, pois isso pode impedir o uso do índice.

Para tabelas muito grandes, essa abordagem supera com folga qualquer estratégia baseada em autojunção.

Resposta completa para a entrevista

Esta é a consulta completa e refinada que retorna a sequência mais longa de cada usuário com suas datas — a versão a ser escrita no quadro branco.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
),
islands AS (
  SELECT user_id,
    MIN(login_date) AS streak_start,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1
ORDER BY user_id;

Verificação rápida

Selecione a ferramenta correta para o requisito.

Recapitulação

Para calcular a sequência mais longa por usuário:

  • Forme ilhas com a âncora login_date - ROW_NUMBER().
  • Reduza cada ilha ao comprimento e ao intervalo de datas.
  • Para obter somente o comprimento, use MAX(streak_len) agrupado por usuário.
  • Para obter também as datas, classifique as ilhas por usuário e mantenha a classificação 1 — use RANK para incluir empates e ROW_NUMBER para escolher um único vencedor.
  • Faça LEFT JOIN com users para exibir usuários com sequência zero.

A seguir: detectar N linhas consecutivas que atendem a uma condição.

Perguntas Frequentes

A aula “Maior sequência por usuário” é grátis?

Sim — o texto completo de “Maior sequência por usuário” é 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 “Maior sequência por usuário”?

Calculando o comprimento máximo de uma sequência consecutiva dentro de cada grupo. 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 “Maior sequência por usuário”?

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

  1. Detectando dias consecutivos no calendário
  2. Maior sequência por usuário
  3. N linhas consecutivas que atendem a uma condição
  4. Sequência ativa atual até hoje
← Voltar para SQL Interview Prep