Entrevista de SQL: Sesionizar eventos de usuario con un intervalo de inactividad de 30 minutos

Problema y contexto aplicable

Asume esta tabla de eventos en PostgreSQL:

CREATE TABLE events (
  event_id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  occurred_at timestamptz NOT NULL
);

Ordena los eventos de cada usuario por occurred_at y event_id. El primer evento abre una sesión. Cada evento posterior abre una nueva sesión cuando su intervalo respecto al evento anterior es mayor o igual a 30 minutos. Devuelve user_id, un session_seq con base en uno, session_start, session_end, event_count y session_duration. Un intervalo exacto de 30 minutos inicia una nueva sesión; ese límite es parte del contrato.

Este patrón aparece en el procesamiento de clickstream, analítica de producto y embudos de comportamiento. No es el mismo problema que encontrar días consecutivos de inicio de sesión. Una racha diaria compara fechas de calendario adyacentes; la sesionización compara el tiempo transcurrido entre eventos adyacentes de un mismo usuario. La solución principal calcula un resultado exacto sobre un conjunto de eventos completo y lógicamente deduplicado. Las consultas acotadas y la materialización incremental requieren reglas de límite adicionales.

Qué evalúa el entrevistador

La primera señal es si el candidato convierte la prosa en un contrato ejecutable. > 30 minutes y >= 30 minutes asignan el evento límite de forma diferente. Comparar con el evento anterior y comparar con el primer evento de una sesión también son definiciones distintas. Este enunciado usa intervalos adyacentes, por lo que una sesión continuamente activa puede durar mucho más de 30 minutos.

La segunda señal es la descomposición en tres etapas de ventana: usar LAG() para leer el predecesor, marcar los límites de sesión y ejecutar un SUM() acumulativo sobre esas marcas. Los cálculos de ventana requeridos no pueden anidarse arbitrariamente en una sola expresión de PostgreSQL. Los CTEs separados también exponen cada relación intermedia para su inspección.

La tercera señal es el ordenamiento determinístico. Dos eventos pueden compartir un occurred_at. Ordenar solo por tiempo deja su orden relativo sin especificar. Agregar el event_id único le da a LAG() y a la suma acumulativa el mismo orden total. El tiempo transcurrido entre timestamps iguales es cero, por lo que permanecen en una misma sesión.

La cuarta señal es la semántica del tiempo. Un timestamptz denota un instante absoluto adecuado para la comparación de tiempo transcurrido. Convertir a hora local antes de restar puede inyectar un salto por horario de verano. La conversión a zona con nombre pertenece al momento de presentación, no a este cálculo de límite de sesión.

Por último, una respuesta sólida reconoce que los datos tardíos pueden reescribir la historia. Un evento insertado en el medio de la línea temporal puede conectar dos sesiones que antes estaban separadas. Un sistema incremental por tanto no puede tratar session_seq como de solo adición; necesita recomputación acotada, correcciones versionadas o una marca de agua de finalidad explícita.

Preguntas a aclarar antes de responder

• ¿Qué lado posee el límite de 30 minutos? Este enunciado inicia una nueva sesión en >= 30 minutes. Una

regla de producto con estrictamente-mayor-que cambia un operador y todas las expectativas de límite.

• ¿Comparamos eventos adyacentes o el primer evento de la sesión? Eventos adyacentes aquí. Una duración máxima de

sesión requiere un estado separado anclado al inicio de la sesión.

• ¿Cómo se manejan los duplicados? event_id es la clave lógica del evento, por lo que las reentre gas deben

deduplicarse antes de esta consulta. Los eventos distintos con el mismo usuario y timestamp siguen siendo filas válidas.

• ¿Qué zona horaria define el intervalo? El tiempo transcurrido entre instantes absolutos. Una zona horaria con nombre cambia

la presentación, no el número de segundos transcurridos.

• ¿La consulta está acotada en el tiempo? El historial completo es sencillo. Un rango de reporte debe especificar

si las sesiones que comenzaron antes del rango se mantienen íntegras y si deben leer el contexto del predecesor.

• ¿Con cuánto retraso pueden llegar los eventos? Una consulta ad hoc puede recomputar. Un resultado materializado necesita un

horizonte de corrección, una marca de agua y un protocolo de actualización o retractación en los consumidores.

• ¿Qué debe devolver una tabla vacía o un usuario con un solo evento? Cero filas para la tabla vacía; una sesión de duración

cero para el usuario con un solo evento.

Marco de respuesta en 30 segundos

"Dentro de cada usuario, creo un orden determinístico por occurred_at, event_id y uso LAG(occurred_at) para obtener el predecesor. Marco la primera fila y cada intervalo de al menos 30 minutos como 1, con todas las demás filas marcadas como 0. Una suma acumulativa sobre un marco explícito de ROWS da una secuencia de sesión con base en uno. Luego agrupo por usuario y secuencia para calcular el inicio, el fin, el conteo y la duración. Las pruebas cubren 29 minutos 59 segundos, exactamente 30 minutos, timestamps empatados, usuarios con un solo evento, IDs duplicados, datos tardíos y el inicio de un rango de reporte. Para la materialización incremental, recomputo el usuario afectado dentro de la ventana de latencia permitida en lugar de asumir que las sesiones solo se agregan."

Análisis profundo paso a paso

Primero obtén el predecesor con una única regla de ordenamiento compartida. event_id no afecta el tiempo transcurrido; solo estabiliza los timestamps iguales:

WITH ordered AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    LAG(occurred_at) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at, event_id
    ) AS previous_at
  FROM events
),
marked AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    CASE
      WHEN previous_at IS NULL THEN 1
      WHEN occurred_at - previous_at >= INTERVAL '30 minutes' THEN 1
      ELSE 0
    END AS is_new_session
  FROM ordered
),
sessionized AS (
  SELECT
    event_id,
    user_id,
    occurred_at,
    SUM(is_new_session) OVER (
      PARTITION BY user_id
      ORDER BY occurred_at, event_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS session_seq
  FROM marked
)
SELECT
  user_id,
  session_seq,
  MIN(occurred_at) AS session_start,
  MAX(occurred_at) AS session_end,
  COUNT(*) AS event_count,
  MAX(occurred_at) - MIN(occurred_at) AS session_duration
FROM sessionized
GROUP BY user_id, session_seq
ORDER BY user_id, session_seq;

El ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW explícito importa. El total acumulativo debe absorber los marcadores de límite fila física por fila física en lugar de heredar la semántica de pares de un marco de ventana predeterminado. El event_id único elimina los pares en esta consulta, pero declarar el marco fija la intención y evita que una futura eliminación del desempate cambie el comportamiento silenciosamente.

Usa estos eventos para verificar el límite:

El usuario 1 tiene dos sesiones: dos eventos en cinco minutos, luego tres eventos en 35 minutos. El intervalo total del usuario 2 es de 58 minutos, pero cada intervalo adyacente está por debajo de 30 minutos, por lo que los tres eventos permanecen en una sola sesión. Esto distingue la sesionización por intervalo adyacente de una duración máxima de sesión.

La corrección se deriva de un invariante simple. La primera fila de un usuario eleva la suma acumulativa de cero a uno. Después de eso, solo una fila que satisfaga el predicado de límite incrementa la suma; cada fila que no es límite la preserva. Dos filas tienen el mismo valor acumulativo exactamente cuando ningún límite marcado las separa. Agrupar por usuario y ese valor produce entonces cada segmento contiguo maximal sin combinar usuarios ni cruzar un límite.

Para N eventos, el plan de ventana normalmente ordena por usuario y tiempo, lo que toma un tiempo de O(N log N); el escaneo de ventana y la agregación son O(N). El estado intermedio es O(N) y puede derramarse a disco. Un índice puede coincidir con el orden lógico:

CREATE INDEX events_session_order_idx
  ON events (user_id, occurred_at, event_id);

El índice no garantiza un plan sin ordenamiento. El costo del escaneo completo, la visibilidad, el paralelismo y los predicados influyen en el optimizador. Inspecciona los escaneos, las ordenaciones, el I/O temporal, las filas estimadas y las filas reales con EXPLAIN (ANALYZE, BUFFERS) sobre datos representativos en lugar de declarar éxito por la presencia del índice.

El filtrado por rango de tiempo es la trampa de corrección más fácil de caer. Si una consulta comienza a las 10:00 mientras un usuario tiene eventos a las 09:50 y 10:10, filtrar primero marca falsamente el evento de las 10:10 como una nueva sesión. Si la salida solo necesita la pertenencia dentro del rango, lee al menos el predecesor más cercano antes del inicio para cada usuario, luego excluye las filas de contexto de la salida. Si la salida debe incluir el inicio completo de la sesión, continúa leyendo hacia atrás hasta encontrar un límite genuino de 30 minutos.

Los datos tardíos también pueden fusionar el historial. Los eventos a las 09:00 y las 09:50 inicialmente forman sesiones separadas. Un evento tardío a las 09:25 cambia ambos intervalos adyacentes a 25 minutos y los fusiona. El procesamiento por lotes puede recomputar la partición afectada. Un sistema incremental debe recomputar por usuario sobre el horizonte de latencia permitido y publicar versiones o retractaciones. El contrato debe indicar si los datos más allá de la marca de agua son puestos en cuarentena, descartados o se les permite desencadenar una corrección más amplia.

Valida cada etapa. Cada previous_at que no sea el primero en ordered debe ser igual al timestamp precedente en el orden compartido. marked contiene 1 solo en las primeras filas y en los límites de umbral. Para cada usuario, session_seq comienza en uno, nunca decrece y aumenta como máximo en uno. La relación final debe tener session_start <= session_end; sus conteos de eventos deben sumar el número de eventos de entrada lógicos; y el intervalo entre sesiones adyacentes debe ser de al menos 30 minutos.

Respuesta de muestra de alta calidad

"Primero confirmaría que el límite es mayor o igual a 30 minutos y que la comparación usa eventos adyacentes. Un event_id único deduplica los eventos lógicos, mientras que los eventos diferentes al mismo tiempo permanecen. El primer CTE ordena cada usuario por tiempo e ID de evento y usa LAG para el timestamp anterior. El segundo marca la primera fila y cada intervalo de umbral. El tercero toma una suma acumulativa sobre un marco explícito de ROWS para crear una secuencia de sesión estable. Luego agrego por usuario y secuencia para el inicio, el fin, el conteo y la duración.

Probaría 29 minutos 59 segundos y exactamente 30 minutos, timestamps iguales, usuarios con un solo evento y una tabla vacía. También probaría un predecesor antes del rango de reporte, porque filtrar antes de LAG crea un falso límite de sesión. El ordenamiento domina con aproximadamente O(N log N). Un índice en (user_id, occurred_at, event_id) puede proporcionar el orden necesario, pero la decisión viene del plan de ejecución consciente del buffer.

Para un resultado materializado continuamente, no trataría los números de sesión como inmutables. Los eventos a las 09:00 y las 09:50 están separados hasta que un evento tardío a las 09:25 los une. El sistema necesita recomputación con alcance por usuario dentro de la ventana de latencia y consumidores conscientes de las versiones. Los eventos más allá de la marca de agua deben seguir una política explícita de cuarentena o corrección más amplia."

Errores comunes

• Restar el primer evento de la sesión del evento actual → Los usuarios continuamente activos se dividen

una vez que la duración total supera los 30 minutos → comparar eventos adyacentes como se especifica.

• Mantener un intervalo exacto de 30 minutos en la sesión anterior → Esto viola el contrato de >= 30 minutes →

escribir una prueba de umbral dedicada.

• Ordenar solo por occurred_at → Los timestamps iguales carecen de un orden total estable → **agregar el

event_id único y reutilizar el orden en ambas ventanas.**

• Omitir el marco explícito de ROWS → El comportamiento de pares predeterminado puede diferir de la acumulación fila por fila →

declarar el marco desde la primera fila hasta la fila actual.

• Restar tiempos de pared locales → Un salto por horario de verano crea u oculta una hora → **comparar

instantes de timestamptz y localizar solo para visualización.**

• Filtrar en el inicio del reporte antes de LAG → El primer evento dentro del rango pierde su predecesor y

se convierte en un falso límite → leer el contexto previo al límite antes de recortar la salida.

• Contar las reentre gas como eventos → event_count se infla → deduplicar por la clave lógica del evento.
• Asumir que las sesiones históricas solo se agregan → Los datos tardíos pueden mover límites o fusionar sesiones →

recomputar un rango acotado y publicar resultados corregibles.

• Asumir que un índice coincidente elimina todo ordenamiento → El optimizador puede elegir otro plan según el

costo del escaneo y los predicados → inspeccionar un plan representativo y el I/O temporal.

Preguntas de seguimiento y respuestas

Seguimiento 1: ¿Qué cambia si un intervalo exacto de 30 minutos permanece en la sesión anterior?

Cambia el predicado de límite de >= INTERVAL '30 minutes' a > INTERVAL '30 minutes'. El resto del pipeline de ventana no cambia, pero la definición de la métrica y cada fixture de prueba deben cambiar con él. Mantén casos para 29:59, 30:00 y 30:01 para que la política no pueda derivar más adelante.

Seguimiento 2: ¿Es suficiente la solución de suma acumulativa si una sesión puede durar como máximo dos horas?

No. Los pequeños intervalos adyacentes pueden extender una sesión indefinidamente, por lo que el siguiente límite también depende del inicio dinámico de la sesión. Un CTE recursivo, una máquina de estados ordenada o un estado por usuario en un procesador de flujo suele ser más claro. Primero distingue una ventana fija de dos horas de "dos horas después del primer evento de la sesión"; esos son contratos diferentes.

Seguimiento 3: ¿Cómo corregirías un resultado materializado para un evento que llega con 24 horas de retraso?

Localiza el evento por user_id, lee un rango que abarque al menos un límite confirmado a cada lado del timestamp tardío, recomputa ese segmento y compáralo con la versión anterior. La salida necesita claves de negocio estables y versiones para que los consumidores puedan aplicar actualizaciones, fusiones y retractaciones de forma idempotente. Si la marca de agua prohíbe cambiar la salida de hace 24 horas, pon el evento en cuarentena y expón una señal de calidad de datos en lugar de ignorarlo silenciosamente.

Seguimiento 4: ¿Cómo optimizarías esto para miles de millones de eventos?

Lee particiones de tiempo que puedan podarse y explota el orden de (user_id, occurred_at, event_id) para reducir el ordenamiento. Un trabajo periódico lleva el último evento de cada usuario y el estado de la sesión abierta a través de los límites de partición para que un límite de archivo o de fecha no se convierta en un límite de sesión. Valida el diseño con sesgo real de usuario, derrame en el ordenamiento, bytes escaneados y latencia de extremo a extremo. Un único usuario super-activo puede requerir una ruta ordenada dedicada.

Seguimiento 5: ¿Cómo demuestras que no se omitió ni se contó dos veces ningún evento?

Usa verificaciones de conservación. La suma de los valores finales de event_count debe ser igual al conteo de filas de entrada deduplicadas. Cada event_id se mapea exactamente a un (user_id, session_seq). Las secuencias de sesión comienzan en uno y aumentan contiguamente por usuario. Los intervalos adyacentes dentro de una sesión están por debajo de 30 minutos, mientras que los límites entre sesiones son de al menos 30 minutos. Luego ejecuta pruebas de propiedades con entrada desordenada, reentre gas, timestamps iguales, bordes de partición y fusiones por eventos tardíos.