Una función de ventana calcula un valor para cada fila a partir de filas relacionadas, sin agruparlas. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) numera los pedidos de cada cliente del más reciente al más antiguo; SUM(x) OVER (ORDER BY day) es un total acumulado; LAG(x) OVER (...) lee la fila anterior.
Para filtrar por una función de ventana, BigQuery admite QUALIFY, que se ejecuta después de calcular las ventanas:
SELECT customer_id, order_id, order_date
FROM shop.orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;
Así se conserva el último pedido de cada cliente: es la forma habitual de eliminar duplicados. Si dos filas empatan en la ordenación, añade un criterio de desempate, aquí order_id, para que el resultado no cambie entre ejecuciones. En cambio, RANK y DENSE_RANK conservan los empates.
Cada producto clasificado por precio dentro de su tipo de tueste.