Nivel 10 · Depura: ¿los clientes repiten pedido cada dos horas?

LAG mira la fila anterior y LEAD la siguiente. Ambos solo tienen sentido cuando “anterior” y “siguiente” se quedan dentro del grupo correcto.

Detectar el fallo

Ejecuta la CTE gaps por sí sola y mira unas cuantas filas en orden: las filas vecinas pertenecen a clientes distintos. Sin PARTITION BY, la ventana es toda la tabla ordenada por tiempo, así que LEAD devuelve la fecha del siguiente pedido que hizo cualquiera. Con unos 10,800 pedidos completados a lo largo de tres años, eso suele ser el mismo día, de ahí el promedio de 0.1 días.

Dos señales lo delatan: un intervalo físicamente implausible y un conteo de “tiene un siguiente pedido” que es casi todos los pedidos. Solo un pedido de toda la tabla no tiene sucesor en la ventana del starter, mientras que en la realidad el último pedido de cada cliente no tiene ninguno.

LAG y LEAD juntos

SELECT order_id,
       CAST(order_date AS DATE) AS order_day,
       LAG(CAST(order_date AS DATE)) OVER w AS prev_day,
       LEAD(CAST(order_date AS DATE)) OVER w AS next_day
FROM orders
WHERE status = 'completed' AND customer_id = 125
WINDOW w AS (PARTITION BY customer_id ORDER BY order_date, order_id);

Para el cliente 125 esto lista ocho pedidos completados, del 2023-01-01 al 2025-04-20. Los intervalos van de 2 días (del 30 de noviembre al 2 de diciembre de 2024, en torno al Black Friday) a casi 11 meses, por eso un promedio sobre muchos clientes es más útil que cualquier intervalo suelto. Revisar a un cliente así, con la partición en su sitio, es la forma más rápida de ver si una ventana se comporta bien antes de agregarla.

Misma ventana, dos direcciones: LAG responde “cuánto tiempo desde el último pedido”, LEAD “cuánto hasta el siguiente”. La cláusula WINDOW con nombre evita repetir la definición. En el primer pedido prev_day es NULL, en el último next_day es NULL.

Errores comunes

• Sin PARTITION BY en una ventana por entidad.
• Restar marcas de tiempo en lugar de fechas: obtienes un intervalo con horas, no días completos.
• Filtrar el periodo antes de la ventana. Restringir primero a 2025 oculta un pedido de diciembre de 2024 que era el verdadero pedido anterior de un cliente.

En SQL Server

LEAD y LAG se escriben igual. Para diferencias de días usa DATEDIFF(day, order_day, next_order_day); restar dos fechas directamente no funciona allí. La cláusula WINDOW solo existe desde SQL Server 2022.