Coding Interview Prep · Lección

Segmentos con cambios de fecha y estado

Agrupe periodos consecutivos con el mismo estado, una pregunta habitual sobre estados de suscripción

Segmentos con cambios de fecha y estado es una lección gratuita de Coding Interview Prep en CoddyKit. Esta es la lección 4 de 4. Puedes leer la lección completa abajo gratuitamente — luego la practicas en el navegador con un editor de código integrado y un tutor de IA 24/7. Forma parte de la ruta de aprendizaje de Coding Interview Prep, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de Coding Interview Prep incluye 4 lecciones en total.

Islas definidas por un valor cambiante

La variante de huecos e islas más relevante para el negocio agrupa filas consecutivas que comparten el mismo estado, transformando un registro de eventos ruidoso en periodos de estado claros. Una formulación clásica es: «Dado un registro de eventos de suscripción, devuelva una fila por cada periodo continuo en el que el usuario permaneció en cada estado».

Aquí la adyacencia no significa «los valores difieren en 1». Significa que el estado no cambia respecto a la fila anterior. Una nueva isla comienza en el momento en que cambia el estado. En este caso, la técnica basada en LAG supera al truco puro de los números de fila.

El ejemplo de suscripción

Considere una tabla sub_events de un usuario, ordenada por fecha:

2026-01-01 activo
2026-02-01 activo
2026-03-01 en pausa
2026-04-01 activo
2026-05-01 activo

El resultado deseado son tres periodos de estado: activo de enero a febrero, en pausa en marzo y activo de abril a mayo. Observe que los dos tramos activos son islas separadas porque un periodo de pausa los interrumpe. El mismo estado, cuando no es consecutivo, corresponde a islas distintas.

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');

Marcar dónde cambia el estado

Use LAG para comparar el estado de cada fila con el anterior. Cuando sean distintos (o el anterior sea NULL en la primera fila), comienza una nueva isla. Emitimos un 1 cuando hay un cambio y un 0 en caso contrario.

Ordene estrictamente por fecha dentro del usuario. Para nuestros datos, las marcas de cambio son 1,0,1,1,0, que señalan los límites de los tres periodos.

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;

Convertir la suma acumulada en una clave de periodo

Como antes, una suma acumulada de las marcas de cambio produce una clave de grupo constante dentro de cada periodo de estado: 1,1,2,3,3 para nuestras filas. Cada clave distinta corresponde a un periodo continuo.

El truco de la diferencia de números de fila no funciona aquí porque el estado no es un número que avance de 1 en 1; la receta de LAG más suma acumulada es la herramienta adecuada cuando la adyacencia significa «valor sin cambios».

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;

Agrupar en periodos de estado

Ahora use GROUP BY con user_id, status y la clave de la suma acumulada para informar del intervalo de cada periodo. Incluir el estado en GROUP BY es seguro porque es constante dentro de un periodo y permite seleccionarlo sin una función de agregación.

El resultado consta exactamente de tres filas: activo del 01-01 al 02-01, en pausa del 03-01 al 03-01 y activo del 04-01 al 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;

De los eventos a los intervalos semiabiertos

Un detalle sutil de las entrevistas: la fecha de un evento indica cuándo comenzó un estado, y el periodo termina realmente cuando comienza el estado siguiente, no en la fecha del último evento con el mismo estado. El final correcto del periodo suele ser el inicio del periodo siguiente, modelado como un intervalo semiabierto [start, next_start).

Calcule el inicio del periodo siguiente con LEAD sobre los periodos agrupados, dejando abierto el periodo final (NULL o 'current').

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;

Gestión de estados repetidos consecutivamente

¿Qué ocurre si el registro contiene filas redundantes como active, active, active, sin ningún cambio entre ellas? El indicador de cambio vale 0 para las repeticiones, por lo que la suma acumulada las mantiene automáticamente en una misma isla. Ese es el comportamiento deseado: los estados idénticos consecutivos se agrupan en un solo periodo.

Esta deduplicación natural de las repeticiones es una ventaja clave del método del indicador de cambio y conviene mencionársela al entrevistador.

Cuándo las brechas de tiempo deben dividir un periodo

A veces, que el estado sea el mismo no es suficiente; una gran brecha de tiempo también debe dividir el periodo, aunque el estado sea idéntico. Por ejemplo, active en enero y active de nuevo después de seis meses de silencio podrían contar como dos periodos.

Amplíe el indicador de cambio con una segunda condición: inicie una nueva isla cuando cambie el estado o cuando el tiempo desde el evento anterior supere un umbral. Así se combinan ambas reglas de adyacencia 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

Contar cambios de estado distintos

Una pregunta de seguimiento natural sería: "¿Cuántas veces cambió de estado este usuario?" Esto equivale simplemente al número de indicadores de cambio menos el primero (que marca el estado inicial, no un cambio).

De forma equivalente, es el número de periodos menos 1. La clave de suma acumulada ya codifica esta información, por lo que la respuesta se obtiene con la misma estructura que construyó para los periodos.

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 qué aquí es mejor que las autocombinaciones

Una solución con una autocombinación para obtener periodos de estado tendría que emparejar cada fila con su vecina, detectar los cambios y después unir los límites; sería un proceso de varios pasos propenso a errores que presenta dificultades con tres o más periodos.

La secuencia LAG-indicador-suma acumulada-GROUP BY gestiona cualquier número de periodos en una sola pasada y sin combinaciones. Expresar este contraste —una sola pasada lineal frente a una autocombinación cuadrática— es exactamente el tipo de razonamiento sénior que los entrevistadores valoran.

Una plantilla reutilizable

Memorice esta plantilla de cuatro cláusulas; resuelve toda la familia de problemas de islas de estados cambiando únicamente la prueba de adyacencia en el CASE:

flag: CASE con LAG para detectar una nueva isla.
key: SUM acumulado del indicador, con partición y ordenación.
collapse: GROUP BY de la columna de partición, el estado y la clave.
interval (opcional): LEAD para los finales de periodo semiabiertos.

La misma estructura sirve para enteros, fechas y estados; solo cambia la condición del CASE.

Comprobación rápida

Confirme que comprende la regla de agrupación de las islas de estados.

Resumen: islas de estados y fechas

Ahora puede resolver la variante más completa de brechas e islas:

La adyacencia significa que el estado no cambia respecto a la fila anterior; el indicador cambia con LAG.
Convierta los indicadores de cambio en una clave de grupo por periodo mediante una suma acumulada.
Utilice GROUP BY user_id, status, key para obtener los intervalos de cada periodo.
Use LEAD para los finales de intervalos semiabiertos; amplíe el indicador para dividirlos cuando haya grandes brechas de tiempo.
Las filas idénticas repetidas se agrupan automáticamente; los recuentos de cambios se obtienen a partir de los mismos indicadores.
Una plantilla reutilizable cubre enteros, fechas y estados; solo cambia el CASE.

Con esto termina el curso de brechas e islas, una señal sólida de nivel sénior en entrevistas de SQL.

Preguntas frecuentes

¿La lección «Segmentos con cambios de fecha y estado» es gratis?

Sí — el texto completo de «Segmentos con cambios de fecha y estado» es gratis para leer aquí en la web. Para practicarla de forma interactiva (editor de código integrado y tutor de IA 24/7) y desbloquear el resto del curso de Coding Interview Prep, actualiza a CoddyKit PRO. El curso de Coding Interview Prep incluye 4 lecciones en total.

¿Qué aprenderé en «Segmentos con cambios de fecha y estado»?

Agrupe periodos consecutivos con el mismo estado, una pregunta habitual sobre estados de suscripción Practicas Coding Interview Prep con código real que ejecutas directamente en el navegador, y un tutor de IA 24/7 responde tus preguntas mientras trabajas en la lección.

¿Necesito experiencia previa para empezar Coding Interview Prep?

No se requiere experiencia previa. Coding Interview Prep en CoddyKit está estructurado para principiantes hasta estudiantes avanzados, así que puedes empezar aquí o desde el inicio y avanzar a tu ritmo. Esta es la lección 4 de 4.

¿Cuánto tiempo toma la lección «Segmentos con cambios de fecha y estado»?

La mayoría de las lecciones de CoddyKit toman alrededor de 5–10 minutos. Cada una es compacta e interactiva, así que avanzas constantemente y retomas exactamente por donde dejaste en la web y la app.

¿Puedo escribir y ejecutar código en esta lección de Coding Interview Prep?

Sí. Cada lección de Coding Interview Prep incluye un editor de código integrado, así que escribes y ejecutas código real directamente en tu navegador y obtienes retroalimentación instantánea de IA — sin configuración local necesaria.

Todas las lecciones de este curso

Reconocer un problema de huecos y segmentos
El truco de la diferencia de números de fila
Encontrar huecos en una secuencia
Segmentos con cambios de fecha y estado

← Volver a Coding Interview Prep