0Pricing
SQL Interview Prep · Lección

Racha más larga por usuario

Calcular la longitud máxima de una secuencia consecutiva dentro de cada grupo.

Racha más larga por usuario es una lección gratuita de SQL Interview Prep en CoddyKit. Esta es la lección 2 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 SQL Interview Prep, y tu progreso se sincroniza en la web y la app de CoddyKit. El curso de SQL Interview Prep incluye 4 lecciones en total.

La pregunta

Una pregunta de seguimiento frecuente tras detectar días consecutivos es: "¿Cuál es la racha más larga de días consecutivos activos de cada usuario?" Los equipos de producto y crecimiento la plantean constantemente para medir la participación.

Ya sabe identificar cada secuencia. El nuevo paso consiste en encontrar la duración máxima por usuario y, a menudo, devolver también las fechas de esa mejor racha. Esta lección se basa directamente en la estructura de brechas e islas.

Recordar cómo se construyen las islas

En la lección anterior, la agrupación por secuencia utilizaba login_date - ROW_NUMBER() como referencia de la isla. Cada usuario puede tener varias islas; primero calcularemos una fila por isla y después las reduciremos a una fila por usuario.

Tenga presente este plan en dos capas: primero construir las islas y después agregarlas.

WITH numbered AS (
  SELECT user_id, login_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id ORDER BY login_date
    ) AS rn
  FROM logins
)
SELECT user_id, login_date - rn AS grp
FROM numbered;

Una fila por isla

Reduzca cada isla a una única fila de resumen que contenga su duración y su intervalo de fechas. Agrupe por usuario y por referencia, y calcule las métricas.

Llamaremos a este CTE islands para que la siguiente capa pueda leerlo con claridad.

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;

Respuesta sencilla: duración máxima

Si el entrevistador solo quiere la duración, el paso final es una consulta de una sola línea: agrupe las islas por usuario y obtenga la duración máxima.

Esta es la respuesta más clara cuando no se requieren las fechas de inicio y final.

-- ...numbered and islands CTEs as before...
SELECT
  user_id,
  MAX(streak_len) AS longest_streak
FROM islands
GROUP BY user_id
ORDER BY user_id;

Devolver también las fechas

A menudo el entrevistador añade: "y muéstreme cuándo ocurrió esa racha". Un MAX simple no puede indicar qué isla ganó. Necesita ordenar las islas dentro de cada usuario y conservar la de rango 1.

Utilice ROW_NUMBER ordenado por duración descendente para que la mejor racha de cada usuario obtenga el rango 1. Añada un criterio de desempate para resolver los empates de forma determinista.

ROW_NUMBER() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC, streak_start ASC
) AS rnk

Ordenar y filtrar

Envuelva la ordenación en un CTE y después filtre con rnk = 1. No puede filtrar directamente una función de ventana en WHERE, por lo que la capa adicional es obligatoria.

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
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1;

RANK frente a ROW_NUMBER en caso de empate

¿Qué ocurre si un usuario tiene dos rachas con la misma duración máxima y el entrevistador quiere que se devuelvan ambas? Sustituya ROW_NUMBER por RANK y conserve rnk = 1.

  • ROW_NUMBER — exactamente una ganadora por usuario (la elección es arbitraria en caso de empate, salvo que añada un criterio de desempate).
  • RANK — todas las rachas más largas empatadas comparten el rango 1 y se conservan.

Aclare qué comportamiento desean; esto demuestra atención a los casos límite.

RANK() OVER (
  PARTITION BY user_id
  ORDER BY streak_len DESC
) AS rnk  -- keep all rnk = 1

Ejemplo resuelto

Suponga que el usuario 7 inició sesión del 1 al 4 de enero, después los días 10 y 11, y finalmente del 20 al 23. Hay tres islas con duraciones de 4, 2 y 4. La duración máxima es 4 y hay un empate.

  • Con ROW_NUMBER y el criterio de desempate streak_start: solo devuelve la secuencia del 1 al 4 de enero.
  • Con RANK: devuelve tanto la secuencia del 1 al 4 de enero como la del 20 al 23.

Explicarlo en voz alta demuestra que ha razonado sobre los duplicados.

Gestión de usuarios sin inicios de sesión

Un entrevistador puede preguntar: "¿Qué ocurre con los usuarios que nunca han iniciado sesión?" Esos usuarios no tienen filas en logins, por lo que desaparecen del resultado. Si deben aparecer con una racha de 0, haga un LEFT JOIN de la tabla completa users y use COALESCE.

SELECT u.user_id,
  COALESCE(MAX(i.streak_len), 0) AS longest_streak
FROM users u
LEFT JOIN islands i ON i.user_id = u.user_id
GROUP BY u.user_id;

Notas de rendimiento

Este patrón hace un único recorrido ordenado de los datos y, después, una agrupación. Para mantener un buen rendimiento:

  • Asegúrese de que exista un índice en (user_id, login_date) para que el ORDER BY de la ventana evite una ordenación.
  • Elimine los duplicados al principio si el origen contiene varios eventos por día.
  • Evite envolver login_date en funciones dentro del ORDER BY, ya que puede impedir el uso del índice.

En tablas muy grandes, este enfoque supera con comodidad a cualquier estrategia basada en self-join.

Respuesta completa para la entrevista

A continuación tiene la consulta completa y pulida que devuelve la racha más larga de cada usuario con sus fechas — la versión que debe escribir en la pizarra.

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
),
ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY streak_len DESC, streak_start
    ) AS rnk
  FROM islands
)
SELECT user_id, streak_start, streak_end, streak_len
FROM ranked
WHERE rnk = 1
ORDER BY user_id;

Comprobación rápida

Elija la herramienta adecuada para el requisito.

Resumen

Para calcular la racha más larga por usuario:

  • Construya islas con el ancla login_date - ROW_NUMBER().
  • Reduzca cada isla a su longitud y rango de fechas.
  • Si solo necesita la longitud, use MAX(streak_len) agrupado por usuario.
  • Si también necesita las fechas, ordene las islas por usuario y conserve la de rango 1 — use RANK para incluir empates y ROW_NUMBER para obtener un único ganador.
  • Haga un LEFT JOIN de users para mostrar los usuarios con una racha de cero.

A continuación: detectar N filas consecutivas que cumplen una condición.

Preguntas frecuentes

¿La lección «Racha más larga por usuario» es gratis?

Sí — el texto completo de «Racha más larga por usuario» 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 SQL Interview Prep, actualiza a CoddyKit PRO. El curso de SQL Interview Prep incluye 4 lecciones en total.

¿Qué aprenderé en «Racha más larga por usuario»?

Calcular la longitud máxima de una secuencia consecutiva dentro de cada grupo. Practicas SQL 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 SQL Interview Prep?

No se requiere experiencia previa. SQL 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 2 de 4.

¿Cuánto tiempo toma la lección «Racha más larga por usuario»?

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

Sí. Cada lección de SQL 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

  1. Detectar días consecutivos del calendario
  2. Racha más larga por usuario
  3. N filas consecutivas que cumplen una condición
  4. Racha activa actual hasta hoy
← Volver a SQL Interview Prep