SQL Interview Prep · Aula

Sequência ativa atual até hoje

Calculando uma sequência em andamento e reiniciando-a quando há uma interrupção.

Aula 4 de 413 etapas

Sequência ativa atual até hoje é 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.

A questão do produto

Recursos de sequência (como os do Duolingo ou do Snapchat) precisam da sequência atual, não da sequência histórica mais longa. A pergunta da entrevista é: "Para cada usuário, quantos dias consecutivos terminando hoje ele esteve ativo? Zere se ele não tiver tido atividade hoje."

Isso acrescenta uma particularidade: a sequência deve estar ancorada em hoje (ou ontem, segundo uma regra de tolerância). Vamos construí-la com base nos fundamentos de lacunas e ilhas que você já conhece.

Definindo a atividade

Primeiro esclareça as regras com o entrevistador — essas decisões alteram a consulta:

A sequência exige atividade hoje ou ontem é aceitável (período de tolerância)?
Vários eventos no mesmo dia são consolidados em um único dia?
Qual fuso horário define um "dia"?

Vamos supor uma linha por dia ativo e que a sequência seja atual se incluir hoje OU ontem.

Formando as ilhas novamente

Reutilize a âncora: numere os dias de cada usuário por data e faça a subtração. Cada sequência consecutiva compartilhará uma âncora. A sequência atual é simplesmente a ilha cujo último dia é hoje ou ontem.

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;

Identificando a ilha mais recente

A ilha mais recente de cada usuário é aquela com o maior streak_end. Verificamos se esse fim é recente o suficiente para contar como uma sequência em andamento.

CURRENT_DATE fornece a data de hoje. CURRENT_DATE - 1 é ontem. Se streak_end for igual a qualquer uma das duas, a sequência está ativa.

WITH /* ...numbered, islands... */
latest AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY streak_end DESC
    ) AS rn2
  FROM islands
)
SELECT * FROM latest WHERE rn2 = 1;

Aplicando o teste de recência

Agora determine se a ilha mais recente está ativa. Se a data final dela for hoje ou ontem, a sequência atual será igual ao seu comprimento; caso contrário, o usuário terá interrompido a sequência e o valor atual será 0.

SELECT user_id,
  CASE
    WHEN streak_end >= CURRENT_DATE - 1
    THEN streak_len
    ELSE 0
  END AS current_streak
FROM latest
WHERE rn2 = 1;

A consulta completa da sequência atual

Reúna todas as camadas: numere, forme as ilhas, selecione a mais recente por usuário e depois aplique o CASE de recência. Esta é a resposta completa.

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,
    MAX(login_date) AS streak_end,
    COUNT(*)        AS streak_len
  FROM numbered
  GROUP BY user_id, login_date - rn
),
latest AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY streak_end DESC
    ) AS rn2
  FROM islands
)
SELECT user_id,
  CASE WHEN streak_end >= CURRENT_DATE - 1
       THEN streak_len ELSE 0 END AS current_streak
FROM latest
WHERE rn2 = 1
ORDER BY user_id;

Exigência de atividade somente hoje

Se a regra de negócio for estrita — a sequência estará ativa somente se o usuário tiver estado ativo hoje — altere a comparação para exigir streak_end = CURRENT_DATE.

A versão com período de tolerância (>= CURRENT_DATE - 1) é mais amigável e comum em aplicativos reais, nos quais o dia ainda não terminou. Confirme sempre qual versão o entrevistador deseja.

CASE WHEN streak_end = CURRENT_DATE
     THEN streak_len ELSE 0 END AS current_streak

Uma alternativa: retroceder

Um modelo mental diferente, que alguns entrevistadores preferem, é calcular a distância entre cada dia e hoje. Um dia fará parte da sequência atual somente se todos os dias entre ele e hoje estiverem presentes. De forma equivalente, compare login_date com CURRENT_DATE - (offset from the latest).

O método das ilhas costuma ser mais simples, mas conhecer uma alternativa demonstra profundidade. A ideia principal é a mesma: uma cadeia ininterrupta ancorada em hoje.

Incluindo usuários inativos

Usuários ausentes de logins não têm uma ilha e desaparecem. Se todos os usuários precisarem apresentar uma sequência (provavelmente 0), faça LEFT JOIN com a lista de usuários e use COALESCE para converter o resultado em 0.

SELECT u.user_id,
  COALESCE(s.current_streak, 0) AS current_streak
FROM users u
LEFT JOIN current_streaks s ON s.user_id = u.user_id;

Armadilhas dos fusos horários

Se a atividade for armazenada como marcas de data e hora, "hoje" dependerá do fuso horário. Faça a conversão antes de truncar para uma data.

Armazene e compare em UTC ou converta primeiro para o fuso horário local do usuário.
Use event_ts AT TIME ZONE 'UTC' AT TIME ZONE user_tz no Postgres e depois converta para date.
Um ::date ingênuo aplicado a uma marca de data e hora em UTC pode colocar um evento noturno no dia errado do calendário.

Mencionar isso evita uma pergunta clássica de acompanhamento sobre essa armadilha.

SELECT user_id,
  (event_ts AT TIME ZONE 'UTC'
             AT TIME ZONE 'America/New_York')::date AS local_day
FROM events;

Desempenho e conclusão

Para painéis atualizados com frequência, essa consulta é executada por usuário em uma única passagem. Para mantê-la rápida:

Crie um índice em (user_id, login_date).
Opcionalmente, restrinja a entrada a datas recentes — uma sequência atual não pode incluir dados mais antigos que seu próprio comprimento, portanto uma janela móvel de, por exemplo, 400 dias é suficiente.
Materialize os dados diários em uma tabela de sequências se ela for lida com muita frequência.

Verificação rápida

Confirme que você entende a âncora em relação ao "agora".

Responda na aula interativa

Recapitulação

Para calcular a sequência ativa atual até hoje:

Forme ilhas com a âncora login_date - ROW_NUMBER().
Selecione a ilha mais recente de cada usuário pelo maior streak_end.
Retorne seu comprimento somente se streak_end for hoje (ou ontem, no período de tolerância); caso contrário, retorne 0.
Faça LEFT JOIN com users para incluir contas inativas; trate os fusos horários antes de truncar para uma data.

Agora você domina todo o conjunto de ferramentas para análise de sequências: detectar sequências, encontrar a sequência mais longa, identificar condições com N elementos consecutivos e calcular a sequência ativa.

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 “Sequência ativa atual até hoje” é grátis?

Sim — o texto completo de “Sequência ativa atual até hoje” é 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 “Sequência ativa atual até hoje”?

Calculando uma sequência em andamento e reiniciando-a quando há uma interrupçã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 4 de 4.

Quanto tempo leva a aula “Sequência ativa atual até hoje”?

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

Detectando dias consecutivos no calendário
Maior sequência por usuário
N linhas consecutivas que atendem a uma condição
Sequência ativa atual até hoje

← Voltar para SQL Interview Prep