Cómo calcular rachas en SQL usando CTE y funciones de ventana
En este artículo presento un método claro y reproducible para calcular rachas en SQL. Ma apoyaré en CTE, funciones de ventana y un calendario independiente que garantiza estabilidad incluso cuando los datos no colaboran.
Medir rachas de asistencia parece sencillo cuando los datos llegan limpios, ordenados y sin inconsistencias. La realidad suele ser distinta: duplicados, fechas fuera de rango, registros repetidos y ruido que nos obliga a construir un enfoque más sólido.
Qué es una racha en SQL y por qué importa
Una racha de asistencia representa la cantidad de días consecutivos en los que una persona ha asistido sin fallar. Esta métrica permite entender continuidad, hábitos y consistencia, y aparece en contextos tan distintos como educación, programas de fidelidad, seguimiento de usuarios o análisis de comportamiento.
En SQL este proceso se implementa con una CTE recursiva que genera el calendario, otra que carga los datos crudos, ROW_NUMBER() para crear el ordinal, y COUNT() OVER para calcular el acumulado. Al final del algoritmo una comparación revela quién conserva la racha.
El problema: datos ruidosos, duplicados y fechas fuera de rango
El dataset utilizado en este ejemplo es ficticio, aunque refleja situaciones comunes: registros duplicados, fechas que no pertenecen al rango de análisis y comensales que aparecen más veces de las necesarias.
Debemos recordar que el objetivo es identificar quién mantiene asistencia perfecta entre el 1 y el 7 de enero de 2024, sin permitir que el ruido afecte el cálculo. Esto implica que el calendario será independiente de la asistencia.
El pipeline paso a paso para las rachas en SQL
Calendario base con CTE recursiva
Se genera una secuencia limpia de fechas que define el marco oficial del análisis. Esta CTE establece el rango y evita que los datos crudos dicten la línea de tiempo.
-- Calendario base: secuencia limpia de fechas válidas para el análisis.
-- Define el rango oficial (2024‑01‑01 → 2024‑01‑07), independiente de la asistencia.
WITH cte_fechas AS (
SELECT CAST('2024-01-01' AS date) AS fecha
UNION ALL
SELECT DATEADD(day, 1, fecha)
FROM cte_fechas
WHERE fecha < '2024-01-07'
),Ingesta de asistencia cruda sin limpieza previa
Los datos se cargan tal como vienen. El pipeline debe resistir duplicados, ruido y fechas fuera del rango. En el mundo real, esto implica que la organización conoce el período oficial de análisis. En nuestro caso Lo hemos acotado para efectos prácticos, porque el objetivo no es discutir la calidad del dataset sino mostrar cómo un diseño robusto permite trabajar incluso cuando los registros no colaboran.
A partir de ese rango, el análisis se apoya en un calendario estable y no en la asistencia. Esta separación evita que los datos crudos impongan la narrativa y garantiza que cualquier irregularidad quede contenida dentro del pipeline.
-- Asistencia cruda: datos reales con duplicados, ruido y fechas fuera de rango.
-- No se corrige nada aquí; el pipeline debe ser robusto ante basura.
cte_asistencia AS (
SELECT *
FROM (VALUES
('2023-12-31','Ana'), ('2023-12-31','Luis'), ('2023-12-31','Maria'),
('2024-01-01','Ana'), ('2024-01-01','Luis'), ('2024-01-01','Maria'),
('2024-01-01','Ruperto'), ('2024-01-01','Ruperto'), ('2024-01-01','Ruperto'),
('2024-01-01','Ruperto'),
('2024-01-02','Ana'), ('2024-01-02','Luis'), ('2024-01-02','Ruperto'),
('2024-01-02','Olinto'),
('2024-01-03','Ana'), ('2024-01-03','Maria'), ('2024-01-03','Luis'),
('2024-01-03','Ruperto'), ('2024-01-03','Maria'),
('2024-01-04','Ana'), ('2024-01-04','Maria'), ('2024-01-04','Ruperto'),
('2024-01-05','Ana'), ('2024-01-05','Luis'),
('2024-01-06','Ana'), ('2024-01-06','Ruperto'),
('2024-01-07','Ana'), ('2024-01-07','Maria'), ('2024-01-07','Ruperto'),
('2024-01-08','Ana'), ('2024-01-08','Maria'), ('2024-01-08','Ruperto')
) AS A(fecha, comensal)
),Eliminación de duplicados
Se conserva una sola fila por fecha y comensal. Los registros repetidos no aportan información adicional: solo inflan las métricas y alteran el acumulado, por lo que se reducen a una única ocurrencia por día y persona.
El resto del dataset permanece intacto. Las fechas fuera de rango y cualquier otra imperfección siguen presentes porque forman parte del escenario que el pipeline procesará sin depender de una limpieza manual.
La intención es demostrar que un diseño bien estructurado puede trabajar con datos imperfectos sin perder precisión ni alterar la narrativa del análisis.
-- Limpieza mínima: una fila por (fecha, comensal). No altera el calendario.
cte_distinct AS (
SELECT DISTINCT
fecha,
comensal
FROM
cte_asistencia
),Generación del ordinal con ROW_NUMBER
Cada fecha del calendario recibe un número consecutivo. Este ordinal representa el día dentro del rango y sirve como referencia para evaluar continuidad.
-- Línea de tiempo oficial: ordinal 1..7 basado solo en el calendario limpio.
cte_orden AS (
SELECT fecha,
ROW_NUMBER() OVER (ORDER BY fecha) AS dia_ordinal
FROM cte_fechas
),Cálculo del acumulado con COUNT OVER
Para cada comensal se calcula cuántas veces ha asistido hasta cada fecha. Este acumulado permite comparar progreso real contra el ordinal.
Antes de continuar, explicaré por qué es importante esto. El acumulado funciona como una cuenta progresiva que refleja cuántos días ha asistido cada persona hasta una fecha determinada. Supongamos, sin pérdida de generalidad, que María asiste el día 1, entonces su acumulado es 1. Si también asiste el día 2, el acumulado pasa a 2 y así sucesivamente. Mientras su acumulado avance al mismo ritmo que el ordinal del calendario, María mantendrá una racha perfecta.
El comportamiento se vuelve más interesante cuando aparece una ausencia. Supongamos que Juan asiste los días 1, 2 y 3. Hasta ese punto, su acumulado coincide con el ordinal: 1‑1, 2‑2, 3‑3. Sin embargo, imaginemos que falta el día 4. Cuando llega el día 5 y Juan reaparece, su acumulado es 4, porque ha asistido cuatro veces en total, pero el ordinal del calendario ya va por 5. Esa diferencia revela que Juan no ha estado presente todos los días. Aunque regrese y siga asistiendo, la racha ya se rompió.
Una sola discrepancia entre acumulado y ordinal basta para descartar a un comensal del grupo que mantiene asistencia perfecta. No importa si vuelve a asistir de manera regular; el algoritmo registra que en algún punto perdió continuidad. Esa es precisamente la fortaleza del método: no necesita revisar manualmente cada ausencia ni aplicar reglas adicionales. La comparación entre ambas secuencias —la del calendario y la del comportamiento real— deja en evidencia cualquier interrupción.
-- Racha acumulada por comensal: cuántas veces ha asistido hasta cada fecha.
-- Se compara luego contra el ordinal para detectar asistencia perfecta.
cte_win AS (
SELECT
a.fecha,
o.dia_ordinal,
a.comensal,
COUNT(1) OVER (
PARTITION BY a.comensal
ORDER BY a.fecha
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS acumulado
FROM cte_distinct a
JOIN cte_orden o ON o.fecha = a.fecha
),Identificación de rachas en SQL
Entendido el punto anterior, reforzaremos la idea: cuando ambos valores coinciden, significa que la persona no ha fallado ningún día hasta ese punto. Esa coincidencia es la señal matemática de una racha perfecta.
-- Sobrevivientes: comensales cuyo acumulado coincide con el ordinal.
cte_descarte AS (
SELECT fecha, comensal
FROM cte_win
WHERE acumulado = dia_ordinal
)Consulta final
Devuelve, para cada fecha del calendario, cuántos comensales siguen con asistencia perfecta y quiénes son.
-- Resultado final: por día, cuántos siguen con asistencia perfecta
-- y lista separada por comas de esos comensales.
SELECT
f.fecha,
COALESCE(COUNT(d.comensal), 0) AS comensales_en_todos_los_dias,
COALESCE(STRING_AGG(d.comensal, ', '), '') AS lista_comensales
FROM cte_fechas f
LEFT JOIN cte_descarte d ON d.fecha = f.fecha
GROUP BY f.fecha
ORDER BY f.fecha;Con el resultado en mano, vale la pena revisar por qué este enfoque se mantiene estable incluso cuando los datos no lo están. Acá puede descargar el script completo, con comentarios en inglés y español
Por qué este método funciona
El enfoque se sostiene porque el calendario no depende de los datos crudos. La asistencia puede venir con ruido, duplicados o fechas incorrectas, pero el análisis se mantiene estable. La comparación entre ordinal y acumulado permite detectar continuidad sin necesidad de CASE complejos ni validaciones adicionales. Además, este patrón se adapta con facilidad a otros problemas de continuidad y resulta ideal para enseñar SQL de forma clara y progresiva.
Extensiones posibles para las rachas en SQL
Este método puede ampliarse para calcular rachas consecutivas, no solo perfectas. También puede parametrizarse el rango de fechas, integrarse con tablas reales o representarse visualmente en dashboards. La estructura modular del pipeline facilita estas variaciones sin alterar la lógica central. Estas variaciones amplían el alcance del método sin alterar su esencia.
Una observación interesante es que este mismo objetivo también puede lograrse con una variación del enfoque presentado en el artículo Detectar días consecutivos en SQL. Con un pequeño ajuste en la lógica de continuidad —adaptando la detección de rupturas al calendario oficial— es posible construir una solución equivalente para el problema de asistencia perfecta. Dejo esa implementación a criterio del lector, como un ejercicio natural para profundizar en ambos métodos y comparar sus ventajas según el contexto.
Conclusión
El cálculo de rachas de asistencia con SQL se vuelve más claro cuando se separa el calendario de los datos crudos y se aprovechan las funciones de ventana. Este enfoque permite trabajar con datasets imperfectos sin perder precisión y ofrece un patrón reutilizable para otros análisis de continuidad. Su valor pedagógico es evidente: cada paso aporta claridad y muestra cómo SQL puede resolver problemas reales con elegancia y estructura.
Foto de Allan González – pexels.com
