Coding Interview Prep · Aula

Ilhas com mudanças de data e status

Agrupe períodos consecutivos com o mesmo status, uma pergunta comum sobre estados de assinaturas.

Ilhas com mudanças de data e status é uma aula grátis de Coding 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 Coding Interview Prep, e seu progresso é sincronizado entre a web e o app CoddyKit. O curso de Coding Interview Prep inclui 4 aulas no total.

Ilhas definidas por um valor variável

A variante de lacunas e ilhas mais relevante para os negócios agrupa linhas consecutivas que compartilham o mesmo estado, transformando um registro de eventos ruidoso em períodos de estado bem definidos. Um enunciado clássico é: “Dado um registro de eventos de assinatura, retorne uma linha para cada período contínuo em que o usuário permaneceu em cada estado.”

Aqui, adjacência não significa “os valores diferem em 1”. Significa que o estado permanece inalterado em relação à linha anterior. Uma nova ilha começa no momento em que o estado muda. É nesse caso que a técnica baseada em LAG se destaca em relação à técnica pura de diferença entre números de linha.

O exemplo de assinatura

Considere uma tabela sub_events para um usuário, ordenada por data:

2026-01-01 ativo
2026-02-01 ativo
2026-03-01 pausado
2026-04-01 ativo
2026-05-01 ativo

O resultado desejado são três períodos de estado: ativo em jan-fev, pausado em mar e ativo em abr-mai. Observe que os dois trechos ativos são ilhas separadas, pois um período pausado os interrompe. O mesmo estado, quando não é consecutivo, significa ilhas diferentes.

CREATE TABLE sub_events (
  user_id INT, status TEXT, event_date DATE
);
INSERT INTO sub_events VALUES
 (1,'active','2026-01-01'),(1,'active','2026-02-01'),
 (1,'paused','2026-03-01'),(1,'active','2026-04-01'),
 (1,'active','2026-05-01');

Marcando onde o estado muda

Use LAG para comparar o estado de cada linha com o estado anterior. Quando forem diferentes (ou quando o anterior for NULL, no caso da primeira linha), uma nova ilha começará. Emitimos 1 para uma mudança e 0 nos demais casos.

Ordene estritamente por data dentro do usuário. Para nossos dados, os marcadores de mudança são 1,0,1,1,0, indicando os três limites de período.

SELECT
  user_id, status, event_date,
  CASE
    WHEN status = LAG(status)
      OVER (PARTITION BY user_id ORDER BY event_date)
    THEN 0 ELSE 1
  END AS is_change
FROM sub_events;

Transformando a soma acumulada em uma chave de período

Como antes, uma soma acumulada dos marcadores de mudança produz uma chave de grupo constante dentro de cada período de estado: 1,1,2,3,3 para nossas linhas. Cada chave distinta representa um período contínuo.

A técnica da diferença entre números de linha não funcionará aqui, porque o estado não é um número que avance de 1 em 1; a receita de LAG mais soma acumulada é a ferramenta certa quando adjacência significa “valor inalterado”.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS is_change
  FROM sub_events
)
SELECT user_id, status, event_date,
  SUM(is_change)
    OVER (PARTITION BY user_id ORDER BY event_date) AS grp
FROM flagged;

Consolidando os períodos de estado

Agora use GROUP BY em user_id, status e na chave da soma acumulada para informar o intervalo de cada período. Incluir o estado no GROUP BY é seguro porque ele é constante dentro de um período e permite selecioná-lo sem uma agregação.

O resultado tem exatamente três linhas: ativo de 01-01 a 02-01, pausado de 03-01 a 03-01 e ativo de 04-01 a 05-01.

WITH flagged AS (
  SELECT user_id, status, event_date,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
),
keyed AS (
  SELECT user_id, status, event_date,
    SUM(chg) OVER (PARTITION BY user_id ORDER BY event_date) AS grp
  FROM flagged
)
SELECT user_id, status,
  MIN(event_date) AS period_start,
  MAX(event_date) AS period_end
FROM keyed
GROUP BY user_id, status, grp
ORDER BY user_id, period_start;

Dos eventos aos intervalos semiabertos

Um detalhe sutil de entrevistas: a data de um evento indica quando um status começou, e o período realmente termina quando o próximo status começa, não na data do último evento com o mesmo status. O fim correto do período costuma ser o início do próximo período, modelado como um intervalo semiaberto [início, próximo_início).

Calcule o início do próximo período com LEAD sobre os períodos consolidados, deixando o período final sem fim definido (NULL ou 'atual').

WITH periods AS (
  -- output of the previous collapse step
  SELECT user_id, status, period_start FROM collapsed
)
SELECT user_id, status, period_start,
  LEAD(period_start)
    OVER (PARTITION BY user_id ORDER BY period_start)
    AS period_end_exclusive
FROM periods;

Como lidar com status repetidos em sequência

E se o registro tiver linhas redundantes como ativo, ativo, ativo, sem nenhuma alteração entre elas? O sinalizador de alteração vale 0 para as repetições, então a soma acumulada as mantém automaticamente em uma única ilha. Esse é o comportamento desejado: status idênticos consecutivos são consolidados em um único período.

Essa deduplicação natural das repetições é uma vantagem importante do método do sinalizador de alteração e vale a pena mencioná-la ao entrevistador.

Quando lacunas de tempo devem interromper um período

Às vezes, “mesmo status” não é suficiente; um grande intervalo de tempo também deve interromper o período, mesmo que o status seja idêntico. Por exemplo, o status ativo em janeiro e novamente após seis meses de silêncio pode contar como dois períodos.

Estenda o sinalizador de alteração com uma segunda condição: inicie uma nova ilha quando o status mudar ou quando o tempo desde o evento anterior exceder um limite. Essa composição combina as duas regras de adjacência de forma clara.

CASE
  WHEN status = LAG(status)
         OVER (PARTITION BY user_id ORDER BY event_date)
   AND event_date - LAG(event_date)
         OVER (PARTITION BY user_id ORDER BY event_date) <= 31
  THEN 0 ELSE 1
END AS is_change

Contando mudanças de estado distintas

Uma pergunta complementar natural: "Quantas vezes este usuário mudou de status?" Isso é simplesmente a contagem dos sinalizadores de alteração menos o primeiro (que marca o estado inicial, não uma mudança).

De forma equivalente, é o número de períodos menos 1. A chave da soma acumulada já codifica isso, então a resposta resulta da mesma estrutura que você construiu para os períodos.

WITH flagged AS (
  SELECT user_id,
    CASE WHEN status = LAG(status)
           OVER (PARTITION BY user_id ORDER BY event_date)
         THEN 0 ELSE 1 END AS chg
  FROM sub_events
)
SELECT user_id, SUM(chg) - 1 AS status_switches
FROM flagged GROUP BY user_id;

Por que isso é melhor do que autojunções aqui

Uma solução com autojunção para períodos de status precisaria associar cada linha à sua vizinha, detectar alterações e depois unir os limites — uma sequência de várias etapas, propensa a erros e difícil de lidar com três ou mais períodos.

O pipeline de LAG, sinalização, soma acumulada e agrupamento processa qualquer número de períodos em uma única passagem, sem junções. Explicar esse contraste — uma passagem única linear versus uma autojunção quadrática — é exatamente o tipo de raciocínio sênior que os entrevistadores valorizam.

Um modelo reutilizável

Memorize este modelo de quatro cláusulas; ele resolve toda a família de problemas de ilhas de status, alterando apenas o teste de adjacência no CASE:

sinalizador: CASE com LAG para detectar uma nova ilha.
chave: SUM acumulado do sinalizador, particionado e ordenado.
consolidação: GROUP BY da coluna de partição, do status e da chave.
intervalo (opcional): LEAD para os fins de períodos semiabertos.

O mesmo esqueleto funciona para inteiros consecutivos, datas e status; apenas a condição do CASE muda.

Verificação rápida

Confirme que você compreende a regra de agrupamento de ilhas de status.

Recapitulação: ilhas de status e datas

Agora você consegue resolver a variante mais completa de lacunas e ilhas:

Adjacência = status inalterado em relação à linha anterior; o sinalizador muda com LAG.
Faça a soma acumulada dos sinalizadores de alteração para obter uma chave de agrupamento por período.
Consolide com GROUP BY user_id, status, key para obter os intervalos dos períodos.
Use LEAD para os fins de intervalos semiabertos; estenda o sinalizador para interromper períodos quando houver grandes lacunas de tempo.
Linhas idênticas repetidas são consolidadas automaticamente; as contagens de mudanças resultam dos mesmos sinalizadores.
Um único modelo reutilizável abrange inteiros, datas e status; somente o CASE muda.

Isso conclui o curso de lacunas e ilhas, um indicador confiável de nível sênior em entrevistas de SQL.

Perguntas Frequentes

A aula “Ilhas com mudanças de data e status” é grátis?

Sim — o texto completo de “Ilhas com mudanças de data e status” é 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 Coding Interview Prep, atualize para CoddyKit PRO. O curso de Coding Interview Prep inclui 4 aulas no total.

O que vou aprender em “Ilhas com mudanças de data e status”?

Agrupe períodos consecutivos com o mesmo status, uma pergunta comum sobre estados de assinaturas. Você pratica Coding 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 Coding Interview Prep?

Nenhuma experiência prévia é necessária. Coding 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 “Ilhas com mudanças de data e status”?

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 Coding Interview Prep?

Sim. Cada aula de Coding 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

Reconhecendo um problema de lacunas e ilhas
O truque da diferença de números de linha
Encontrando lacunas em uma sequência
Ilhas com mudanças de data e status

← Voltar para Coding Interview Prep