Viajando en el tiempo: LAG, LEAD y comparaciones

1-- LAG(columna, offset, valor_default) OVER (ORDER BY ...)
2-- offset: cuantas filas atras mirar (default: 1)
3-- valor_default: que devolver si no hay fila anterior (default: NULL)
4
5SELECT
6    fecha,
7    importe,
8    LAG(importe) OVER (ORDER BY fecha) AS importe_anterior,
9    importe - LAG(importe) OVER (ORDER BY fecha) AS variacion,
10    ROUND(
11        100.0 * (importe - LAG(importe) OVER (ORDER BY fecha)) 
12        / LAG(importe) OVER (ORDER BY fecha), 1
13    ) AS variacion_pct
14FROM ventas_mensuales;

LAG(importe) = "el importe de la fila anterior según el ORDER BY"

1-- Para cada pedido: dias hasta el siguiente pedido del mismo cliente
2SELECT
3    cliente_id,
4    fecha,
5    importe,
6    LEAD(fecha) OVER (PARTITION BY cliente_id ORDER BY fecha) AS proxima_compra,
7    DATEDIFF('day', fecha, 
8        LEAD(fecha) OVER (PARTITION BY cliente_id ORDER BY fecha)
9    ) AS dias_hasta_proxima
10FROM pedidos
11WHERE estado = 'completado';

LEAD + PARTITION BY cliente_id = "la próxima compra de ESTE cliente"

1-- Variacion mensual por ciudad
2WITH ventas_mes AS (
3    SELECT
4        c.ciudad,
5        DATE_TRUNC('month', p.fecha) AS mes,
6        SUM(p.importe)::INTEGER AS total
7    FROM clientes c
8    JOIN pedidos p ON p.cliente_id = c.id
9    GROUP BY c.ciudad, DATE_TRUNC('month', p.fecha)
10)
11SELECT
12    ciudad,
13    mes,
14    total,
15    LAG(total) OVER (PARTITION BY ciudad ORDER BY mes) AS mes_anterior,
16    total - LAG(total) OVER (PARTITION BY ciudad ORDER BY mes) AS variacion,
17    CASE
18        WHEN LAG(total) OVER (PARTITION BY ciudad ORDER BY mes) IS NULL THEN 'primer mes'
19        WHEN total > LAG(total) OVER (PARTITION BY ciudad ORDER BY mes) THEN 'crecimiento'
20        WHEN total < LAG(total) OVER (PARTITION BY ciudad ORDER BY mes) THEN 'caida'
21        ELSE 'estable'
22    END AS tendencia
23FROM ventas_mes
24ORDER BY ciudad, mes;

LAG particionado por ciudad: cada ciudad se compara consigo misma, no con las demás.

1-- Detectar cambios de estado en pedidos
2WITH cambios AS (
3    SELECT
4        pedido_id,
5        estado,
6        fecha_cambio,
7        LAG(estado) OVER (PARTITION BY pedido_id ORDER BY fecha_cambio) AS estado_anterior
8    FROM pedido_historico
9)
10SELECT *
11FROM cambios
12WHERE estado != estado_anterior  -- Solo filas donde hubo un cambio
13   OR estado_anterior IS NULL;   -- O es el primer registro

LAG para detección de cambios: si el valor actual difiere del anterior, algo pasó.

1-- Comparar cada vendedor con el mejor y peor de su equipo
2SELECT
3    equipo,
4    nombre,
5    ventas,
6    FIRST_VALUE(nombre) OVER (
7        PARTITION BY equipo ORDER BY ventas DESC
8    ) AS lider_equipo,
9    FIRST_VALUE(ventas) OVER (
10        PARTITION BY equipo ORDER BY ventas DESC
11    ) AS ventas_lider,
12    ventas - FIRST_VALUE(ventas) OVER (
13        PARTITION BY equipo ORDER BY ventas DESC
14    ) AS gap_vs_lider
15FROM vendedores;

FIRST_VALUE con ORDER BY DESC te da el máximo. Con ASC, el mínimo.