Lección 12.8 · Tiempo de lectura: ~7 min

Los huecos e islas (gaps and islands) son una familia de ejercicios sobre secuencias: encontrar tramos de valores consecutivos (islas) y los agujeros entre ellos (huecos). Preguntas típicas: "la racha de inicios de sesión más larga de cada usuario", "los periodos en los que un sensor envió datos", "los números de factura que faltan". Es una de las preguntas avanzadas favoritas en las entrevistas de SQL. En esta lección aprenderás el truco de ROW_NUMBER que agrupa fechas consecutivas en islas, encontrarás los huecos con LEAD y calcularás la racha más larga de cada cliente.

Huecos e islas en SQL

Una isla es un tramo de valores sin agujeros: días consecutivos, números consecutivos. Un hueco es un tramo de valores que faltan entre dos islas. La dificultad es que SQL trabaja con conjuntos, y "consecutivo" es una relación entre filas. Las funciones de ventana lo convierten en una consulta corta.

Los ejemplos se ejecutan en la base de datos de ejemplo Sakila (MySQL 8.0). Los alquileres de Sakila se registraron en periodos de una semana, así que las islas se ven con facilidad.

El truco clave: fecha menos número de fila

Toma la lista ordenada de fechas distintas y numérala. Dentro de un tramo de días consecutivos, la fecha aumenta un día y el número de fila aumenta uno, así que fecha − número de fila se mantiene constante. Cuando falta un día, la fecha salta hacia delante y el número de fila no, y la diferencia cambia. Esa diferencia es la clave de la isla.

Veámoslo con los días de alquiler del cliente 42 antes del 13 de julio de 2005:

WITH customer_days AS (
    SELECT DISTINCT DATE(rental_date) AS rental_day
    FROM rental
    WHERE customer_id = 42
      AND rental_date < '2005-07-13'
)
SELECT rental_day,
       ROW_NUMBER() OVER (ORDER BY rental_day) AS rn,
       DATE_SUB(rental_day, INTERVAL ROW_NUMBER() OVER (ORDER BY rental_day) DAY) AS island_key
FROM customer_days
ORDER BY rental_day;

Resultado: 9 días que forman 4 islas; por ejemplo, del 9 al 12 de julio todos tienen la clave 2005-07-03.

La clave en sí no significa nada; solo tiene que ser igual dentro de una isla y distinta entre islas. Falta el 8 de julio, así que el 7 de julio forma su propia isla.

Islas: periodos con alquileres

Ahora agrupa por la clave y toma el primer y el último día de cada isla:

WITH rental_days AS (
    SELECT DISTINCT DATE(rental_date) AS rental_day
    FROM rental
),
grouped AS (
    SELECT rental_day,
           DATE_SUB(rental_day, INTERVAL ROW_NUMBER() OVER (ORDER BY rental_day) DAY) AS island_key
    FROM rental_days
)
SELECT MIN(rental_day) AS island_start,
       MAX(rental_day) AS island_end,
       COUNT(*) AS days
FROM grouped
GROUP BY island_key
ORDER BY island_start;

Resultado: 6 islas: cinco periodos de 8 días consecutivos en 2005 y un solo día, el 14 de febrero de 2006.

El DISTINCT de la primera CTE es imprescindible. Si un día apareciera dos veces, recibiría dos números de fila y la diferencia rompería la isla. Para secuencias de enteros, usa value - ROW_NUMBER() de la misma forma.

Huecos: periodos sin alquileres

Para los huecos, compara cada día con el siguiente usando LEAD(). Si el día siguiente llega más de un día después, hay un agujero entre ellos:

WITH rental_days AS (
    SELECT DISTINCT DATE(rental_date) AS rental_day
    FROM rental
),
with_next AS (
    SELECT rental_day,
           LEAD(rental_day) OVER (ORDER BY rental_day) AS next_day
    FROM rental_days
)
SELECT DATE_ADD(rental_day, INTERVAL 1 DAY) AS gap_start,
       DATE_SUB(next_day, INTERVAL 1 DAY) AS gap_end,
       DATEDIFF(next_day, rental_day) - 1 AS missing_days
FROM with_next
WHERE DATEDIFF(next_day, rental_day) > 1
ORDER BY gap_start;

Resultado: 5 huecos: cuatro de 13 días entre los periodos de 2005 y uno de 174 días, del 24 de agosto de 2005 al 13 de febrero de 2006.

La misma consulta con números encuentra ids que faltan, por ejemplo números de factura saltados: cambia DATEDIFF(next_day, rental_day) por next_id - id.

Rachas por cliente

La versión de entrevista: "encuentra la racha más larga de días consecutivos con alquileres de cada cliente". Agrega PARTITION BY customer_id para que cada cliente tenga su propia numeración y luego agrega dos veces: las islas por cliente y después la isla más larga:

WITH customer_days AS (
    SELECT DISTINCT customer_id, DATE(rental_date) AS rental_day
    FROM rental
),
grouped AS (
    SELECT customer_id, rental_day,
           DATE_SUB(rental_day, INTERVAL ROW_NUMBER() OVER (
               PARTITION BY customer_id ORDER BY rental_day) DAY) AS island_key
    FROM customer_days
),
streaks AS (
    SELECT customer_id, island_key, COUNT(*) AS streak_days
    FROM grouped
    GROUP BY customer_id, island_key
)
SELECT customer_id, MAX(streak_days) AS longest_streak
FROM streaks
GROUP BY customer_id
ORDER BY longest_streak DESC, customer_id
LIMIT 5;

Resultado: los clientes 42, 181, 290, 306 y 373 alquilaron 8 días seguidos; en total, 10 clientes tienen una racha de 8 días.

La racha más larga posible aquí es de 8 días, porque la propia tienda solo tuvo alquileres en periodos de 8 días. Con datos reales de actividad, la misma consulta encuentra rachas de inicios de sesión, días laborables consecutivos o periodos de datos continuos de un sensor.

Práctica

• Problema Gap & Islands

Conclusiones clave:

• Las islas son tramos de valores consecutivos; los huecos, los tramos que faltan entre ellas.
• En un tramo de fechas consecutivas, date - ROW_NUMBER() es constante: agrupa por ese valor para obtener las islas.
• Quita primero los repetidos con DISTINCT, o un día recibirá varios números de fila y las islas se romperán.
• LEAD() compara cada valor con el siguiente y encuentra los huecos.
• PARTITION BY da a cada entidad sus propias islas; una segunda agregación encuentra la racha más larga.

Preguntas frecuentes

¿Qué es el problema gaps and islands en SQL?

Es una clase de ejercicios sobre secuencias de valores: las islas son tramos de valores consecutivos (días, números) sin agujeros, y los huecos son los valores que faltan entre ellas. Ejemplos: rachas de inicios de sesión, periodos continuos de actividad, números de factura que faltan.

¿Cómo encuentro días consecutivos en SQL?

Numera las fechas distintas con ROW_NUMBER() OVER (ORDER BY day) y resta ese número de días a la fecha. Los días consecutivos obtienen el mismo resultado, así que un GROUP BY por ese valor con MIN(day), MAX(day) y COUNT(*) da cada tramo.

¿Cómo encuentro fechas o números que faltan en SQL?

Usa LEAD(value) OVER (ORDER BY value) para obtener el valor siguiente de cada fila y conserva las filas donde la diferencia es mayor que un paso. El hueco va del valor más un paso al valor siguiente menos un paso. Para enumerar cada fecha que falta, une un calendario generado (lección 6.6).

¿Cómo encuentro la racha más larga de cada usuario?

Agrupa los días consecutivos en islas por usuario con ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY day), cuenta los días de cada isla y toma el MAX de esos conteos por usuario.