Entrevista de SQL: Encontrar la racha de inicios de sesión consecutivos más larga de cada usuario

Prompt y contexto aplicable

Se le proporciona una tabla de eventos de PostgreSQL:

CREATE TABLE user_logins (
  user_id bigint NOT NULL,
  login_at timestamptz NOT NULL
);

Para cada usuario, devuelva cada secuencia más larga de fechas consecutivas del calendario de America/New_York en las que el usuario inició sesión al menos una vez. Las columnas de salida son user_id, streak_start, streak_end y streak_days. Múltiples eventos en la misma fecha local cuentan como un solo día activo. Cualquier fecha local faltante rompe una racha. Si un usuario tiene dos rachas más largas de igual duración, devuelva ambas. Los usuarios sin eventos no aparecen.

Este es un problema de entrevista de datos y SQL, no una solicitud para contar períodos transcurridos de 24 horas. Un día local cercano a una transición de horario de verano puede contener 23 o 25 horas y sigue siendo una sola fecha de calendario. Por lo tanto, la pregunta fija la zona horaria de reporte antes de convertir marcas de tiempo en fechas. La respuesta principal apunta a PostgreSQL; otros dialectos necesitan una aritmética de fechas diferente.

La tarea central es un problema de brechas e islas (gaps-and-islands): transformar fechas ordenadas para que cada fecha en una secuencia consecutiva comparta una clave estable, agregar cada clave en una isla y luego conservar todas las islas empatadas con la longitud máxima por usuario.

Lo que evalúa el entrevistador

La primera señal es si el candidato define el nivel de detalle (grain) antes de escribir una función de ventana. El nivel de detalle de origen es un evento de inicio de sesión, pero el nivel de detalle de negocio es una fila por usuario y fecha de calendario local. Omitir esa conversión permite que los eventos duplicados en el mismo día inflen ROW_NUMBER(), los conteos y los límites de la racha.

La segunda señal es si el candidato puede derivar la clave de la isla. Después de ordenar las fechas distintas, tanto la fecha como ROW_NUMBER() avanzan de uno en uno dentro de una secuencia consecutiva. Restar el desplazamiento del número de fila de cada fecha produce, por lo tanto, el mismo valor a lo largo de esa secuencia. En una brecha, la fecha salta en más de uno mientras que el número de fila avanza exactamente en uno, por lo que la clave cambia.

La tercera señal es la disciplina con el contrato. “Racha más larga” es ambiguo cuando dos secuencias tienen la misma duración. Una consulta que utiliza ROW_NUMBER() para elegir un resultado descarta silenciosamente un empate válido. Este prompt requiere cada máximo empatado, por lo que la respuesta compara la longitud de cada isla con la longitud máxima de isla para ese usuario.

La cuarta señal es la corrección de la zona horaria. Convertir login_at directamente a date utiliza la zona horaria de la sesión de la base de datos, que puede diferir entre entornos. La respuesta convierte primero cada timestamptz a una zona de negocio con nombre y solo entonces toma la fecha. Un desplazamiento UTC fijo es insuficiente para una zona cuyo desplazamiento cambia con las reglas del horario de verano.

La señal final es la verificación y el criterio de escala. Una consulta correcta debe probarse en cada CTE, con eventos duplicados, secuencias de un día, brechas, empates, casos de medianoche local y límites de horario de verano. En una tabla de eventos grande consultada repetidamente, el candidato debe reconocer que reducir los eventos sin procesar a una fila almacenada por usuario-día puede ser más valioso que microoptimizar la consulta final de ventanas.

Preguntas para aclarar antes de responder

• ¿Qué define un día? Una zona horaria de negocio con nombre, UTC o la zona propia de cada usuario cambia la conversión

de fecha y posiblemente la respuesta. Este prompt utiliza America/New_York para cada usuario.

• ¿Los inicios de sesión múltiples en un día cuentan más de una vez? Aquí no cuentan más de una vez, por lo que la deduplicación debe ocurrir

antes de la numeración. Si la métrica fuera de eventos consecutivos en su lugar, el nivel de detalle y la regla de agrupación cambiarían.

• ¿Una fecha faltante siempre rompe la racha? Sí. Una pregunta de sesionización con un umbral de 30 minutos

requiere una comparación con la fila anterior en lugar de una adyacencia estricta en el calendario.

• ¿Cómo se deben devolver los empates? Este contrato devuelve cada isla más larga empatada. Elegir la racha más

reciente requeriría un criterio de desempate explícito diferente.

• ¿El rango está acotado? Un filtro de fecha puede reducir el trabajo, pero también trunca las rachas que comienzan

antes del rango. Quien realiza la consulta debe indicar si el resultado es “dentro del rango” o la racha completa que cruza su límite.

• ¿Pueden user_id o login_at ser nulos? El esquema indica que no. Si se permitieran nulos, su tratamiento

debería especificarse antes de ordenar o agrupar.

• ¿Es esta una consulta única o una métrica de producto recurrente? Una respuesta ad hoc puede escanear y ordenar filas

diarias. Un panel actualizado con frecuencia puede justificar una tabla de usuario-día mantenida de forma incremental.

Estructura de respuesta en 30 segundos

“Primero convertiría cada timestamptz a la zona horaria de negocio acordada y deduplicaría a una fila por usuario y fecha local. Dentro de cada usuario, ordeno esas fechas y asigno ROW_NUMBER(). Para fechas estrictamente consecutivas, login_day - row_number × one day se mantiene constante dentro de una racha y cambia después de una brecha, por lo que agrupo por esa clave derivada para obtener los límites y la longitud de cada racha. Luego comparo cada longitud con el máximo del usuario, lo que preserva los empates. Probaría eventos duplicados en el mismo día, rachas de un día, brechas, máximos iguales, casos de medianoche local y de horario de verano, e inspeccionaría el plan de ejecución en datos representativos.”

Análisis detallado paso a paso

Comience normalizando el flujo de eventos al nivel de detalle del negocio. Para un timestamptz, AT TIME ZONE con una zona nombrada produce la marca de tiempo de reloj de pared en esa zona. Convertir ese resultado a date da la fecha del calendario de negocio. SELECT DISTINCT garantiza entonces exactamente una fila por usuario-día.

La consulta completa es:

WITH login_days AS (
  SELECT DISTINCT
    user_id,
    (login_at AT TIME ZONE 'America/New_York')::date AS login_day
  FROM user_logins
),
numbered AS (
  SELECT
    user_id,
    login_day,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY login_day
    ) AS rn
  FROM login_days
),
grouped AS (
  SELECT
    user_id,
    login_day,
    login_day - (rn * INTERVAL '1 day') AS island_key
  FROM numbered
),
streaks AS (
  SELECT
    user_id,
    MIN(login_day) AS streak_start,
    MAX(login_day) AS streak_end,
    COUNT(*) AS streak_days
  FROM grouped
  GROUP BY user_id, island_key
),
scored AS (
  SELECT
    user_id,
    streak_start,
    streak_end,
    streak_days,
    MAX(streak_days) OVER (PARTITION BY user_id) AS max_streak_days
  FROM streaks
)
SELECT
  user_id,
  streak_start,
  streak_end,
  streak_days
FROM scored
WHERE streak_days = max_streak_days
ORDER BY user_id, streak_start;

La demostración sigue las filas diarias ordenadas. Para un usuario, llame a las fechas distintas d1, d2, ... y a los números de fila 1, 2, .... Si d(i+1) = d(i) + 1 day, restar el desplazamiento del siguiente número de fila elimina el mismo día extra, por lo que las claves derivadas son iguales. Si falta al menos una fecha, d(i+1) avanza dos o más días mientras que el número de fila avanza en uno; la clave derivada aumenta e inicia un nuevo grupo. La deduplicación hace que COUNT(*) sea igual a los días calendario, mientras que MIN y MAX son los límites exactos de la isla.

El máximo final es deliberadamente un MAX de ventana, no otro ROW_NUMBER(). Cada isla cuya longitud sea igual al máximo del usuario sobrevive. Si el producto luego pide solo una racha, agregue una regla declarada como “la fecha de fin más reciente gana” y use un ordenamiento determinista; no invente esa regla dentro de la consulta.

Considere las fechas normalizadas para dos usuarios:

user 1: Mar 07, Mar 08, Mar 09, Mar 11, Mar 12
user 2: Nov 01, Nov 02, Nov 04, Nov 05

result:
user 1 | Mar 07 | Mar 09 | 3
user 2 | Nov 01 | Nov 02 | 2
user 2 | Nov 04 | Nov 05 | 2

El usuario 1 tiene un máximo de tres días. El usuario 2 tiene dos máximos separados de dos días, por lo que se requieren ambas filas. Múltiples eventos sin procesar en cualquiera de las fechas mostradas no cambian el resultado. Alrededor de un cambio de horario de verano, la pregunta relevante sigue siendo si las fechas locales son adyacentes, no si las marcas de tiempo están exactamente a 24 horas de diferencia.

Para N eventos sin procesar y D filas distintas de usuario-día, la deduplicación lee N filas y puede usar hash o sort; el paso de ventana ordena hasta D filas por usuario y fecha. Un límite útil para la entrevista es un tiempo de O(N log N + D log D) en un plan basado en ordenamiento y un espacio intermedio de O(D), teniendo en cuenta que el optimizador puede usar tablas hash, orden preexistente, paralelismo o volcados a disco (disk spills). El plan de ejecución, no solo el Big-O, decide si la consulta de producción es aceptable.

Para una métrica recurrente sobre miles de millones de eventos, cree una tabla mantenida incrementalmente con una clave única en (user_id, login_day). Eso traslada la conversión de zona horaria y la deduplicación del mismo día al límite de ingesta o por lotes, de modo que la consulta de rachas lea D filas diarias en lugar de N eventos. Si una consulta ad hoc tiene un rango de tiempo, aplique límites de marca de tiempo UTC sargables antes de la conversión a fecha local, pero derive esos límites UTC a partir de las medianoches locales de la zona nombrada para que se respeten los cambios de horario de verano.

Inspeccione los resultados intermedios en lugar de tratar la tabla final como prueba:

-- These checks are run against the corresponding CTE or materialized test result.
SELECT user_id, login_day, COUNT(*)
FROM login_days
GROUP BY user_id, login_day
HAVING COUNT(*) > 1;

SELECT *
FROM numbered
ORDER BY user_id, login_day;

SELECT *
FROM streaks
WHERE streak_days <> (streak_end - streak_start + 1);

La primera y tercera verificación no deberían devolver filas. La salida numerada hace visible un nivel de detalle o un ordenamiento incorrectos. Ejecute la consulta completa con el prefijo EXPLAIN (ANALYZE, BUFFERS) para revelar escaneos, ordenamientos, estimaciones de filas, E/S temporal y si reducir los eventos sin procesar antes marcaría una diferencia. Use una copia representativa segura cuando ejecutar la instrucción de producción en sí sea costoso.

La técnica de fecha desplazada no es universal. Si una nueva sesión comienza cuando la brecha supera los 30 minutos, o una isla continúa mientras el valor de un estado permanece sin cambios, use LAG() para inspeccionar la fila anterior, marcar cada límite y tomar una suma acumulada SUM() de esas marcas. La regla de decisión es simple: use la clave desplazada para secuencias estrictas unidad por unidad; use marcas de límite cuando la continuidad dependa de una comparación personalizada.

Respuesta de muestra de alta calidad

“Antes de escribir SQL, fijaría el nivel de detalle y el contrato de empates. El origen tiene muchos eventos por usuario, pero la métrica cuenta una fecha de calendario de America/New_York por usuario. Por lo tanto, convertiría el timestamptz a esa zona nombrada, haría un cast a date y deduplicaría antes de cualquier función de ventana. Esto también evita que la zona horaria de la sesión cambie silenciosamente el resultado.

Para el paso de brechas e islas (gaps-and-islands), asigno ROW_NUMBER() ordenado por fecha local dentro de cada usuario. Durante una secuencia consecutiva, tanto la fecha como el número de fila avanzan de uno en uno, por lo que restar el desplazamiento de días del número de fila produce una clave constante. Una fecha faltante hace que la fecha salte más lejos que el número de fila y cambia la clave. Agrupar por usuario y por esa clave da el inicio, el fin y la cantidad de fechas activas para cada racha.

Usaría un máximo de ventana sobre las longitudes de las rachas y mantendría la igualdad con ese máximo. Eso devuelve todas las rachas más largas empatadas, como se requiere, en lugar de elegir una silenciosamente. El límite superior basado en ordenamiento es aproximadamente O(N log N + D log D), donde N representa los eventos sin procesar y D representa los días-usuario distintos, aunque inspeccionaría el plan real y los volcados a disco.

Mis datos de prueba incluirían varios eventos en una misma fecha, un usuario de un solo día, una fecha faltante, dos máximos iguales, eventos a ambos lados de la medianoche local y una transición de horario de verano. Para una métrica recurrente a gran escala, mantendría una tabla única de usuario-día y ejecutaría la lógica de ventana sobre ese nivel de detalle más pequeño. Si la continuidad cambia de adyacencia de calendario a una brecha de umbral, cambiaría a LAG() junto con marcas de límite y una suma acumulada.”

Errores comunes

• Numerar eventos de inicio de sesión sin procesar → los eventos duplicados avanzan el número de fila e inflan los conteos →

deduplique a una fila de usuario-día antes de aplicar ventanas.

• Hacer cast de timestamptz directamente a date → la respuesta depende de la zona horaria de la sesión →

convierta primero a la zona de negocio nombrada.

• Usar un desplazamiento UTC fijo → las fechas locales se vuelven incorrectas cuando la zona nombrada cambia su desplazamiento →

use una zona IANA con sus reglas de calendario.

• Comparar marcas de tiempo con 24 horas de diferencia → días locales de 23 o 25 horas rompen rachas válidas de calendario

→ compare fechas locales, porque el contrato es de adyacencia de calendario.

• Agrupar solo por la fecha desplazada → los usuarios con la misma clave derivada se fusionan → **agrupe

tanto por user_id como por island_key.**

• Tomar una sola fila con ROW_NUMBER() → se descartan las rachas más largas empatadas → **compare cada isla

con el máximo por usuario.**

• Filtrar un intervalo de reporte sin una regla de límites → una racha que cruza la fecha de inicio queda

truncada y puede etiquetarse incorrectamente → defina si los resultados son locales al rango o islas completas.

• Usar LAG() sin manejar la primera fila → la primera isla carece de límite → **trate una fila

anterior nula como el inicio de un grupo.**

• Citar únicamente Big-O → un volcado de ordenamiento a disco o una mala estimación de cardinalidad permanecen invisibles → **inspeccione

los conteos intermedios y EXPLAIN (ANALYZE, BUFFERS).**

• Escanear el historial sin procesar para cada actualización del panel → la conversión y deduplicación repetidas dominan

el costo → mantenga un nivel de detalle único de usuario-día cuando la carga de trabajo lo justifique.

Preguntas de seguimiento y respuestas

Pregunta de seguimiento 1: ¿Cómo devolvería únicamente la racha más larga más reciente?

Mantenga la misma construcción de islas. Después de calcular las rachas, clasifíquelas por usuario mediante streak_days DESC, luego streak_end DESC, y finalmente streak_start DESC como un último desempate determinista. Devuelva el rango uno. Especifique que esto cambia el contrato de salida: ya no sobreviven todas las longitudes iguales.

Pregunta de seguimiento 2: ¿Qué cambia si una sesión termina tras 30 minutos de inactividad?

La resta de calendario ya no modela la continuidad. Ordene los eventos por marca de tiempo, use LAG(login_at) por usuario, marque la primera fila o cualquier brecha mayor a 30 minutos como una nueva sesión, y calcule una suma acumulada SUM de esa marca con un marco ROWS UNBOUNDED PRECEDING explícito. Agrupe por usuario y el ID de sesión generado.

Pregunta de seguimiento 3: ¿Cómo maneja la zona horaria propia de cada usuario?

Haga un join del evento con un valor de zona horaria del usuario versionado que sea válido para la hora del evento, luego convierta antes de tomar la fecha. Un único ajuste de perfil actual puede reescribir días históricos después de que un usuario se muda. Aclare si el producto desea la actividad histórica congelada bajo la zona horaria vigente en ese momento o recalculada bajo la zona horaria actual del usuario; esas son métricas diferentes.

Pregunta de seguimiento 4: ¿Cómo consultaría únicamente los últimos 90 días locales?

Defina si una racha puede comenzar antes de la ventana. Para resultados locales a la ventana, derive los dos instantes UTC correspondientes a la medianoche local al inicio y al final en la zona nombrada, filtre login_at por esos límites y luego normalice. Para islas completas, incluya suficientes filas diarias precedentes para encontrar la primera brecha real; un corte ciego de 90 días no puede demostrar el verdadero inicio.

Pregunta de seguimiento 5: ¿Cómo haría esto eficiente para un panel diario?

Mantenga user_login_days(user_id, login_day) con una clave única y upserts idempotentes. Actualícela desde el pipeline de eventos utilizando la regla de zona horaria acordada. Recalcule únicamente los usuarios cuyas filas diarias cambiaron, o reconstruya periódicamente a partir de una ventana superpuesta para absorber eventos tardíos. Concilie los conteos de filas diarias con el origen sin procesar antes de publicar.

Pregunta de seguimiento 6: ¿Qué pruebas exigiría antes de pasar a producción?

Utilice conjuntos de pruebas basados en tablas (table-driven fixtures) para duplicados, usuarios de una sola fila, brechas internas, máximos empatados, eventos en la medianoche local, inicio y fin del horario de verano, eventos que llegan tarde y cruces de límites de reporte. Aserte que las filas diarias sean únicas, que cada isla satisfaga streak_days = streak_end - streak_start + 1, y que cada racha devuelta sea igual al máximo de su usuario. Compare la tabla diaria incremental con un recálculo de eventos sin procesar en una muestra de usuarios, luego inspeccione el plan de consulta y la E/S temporal con una cardinalidad similar a la de producción.