Uma função de janela calcula um valor para cada linha a partir de linhas relacionadas, sem agrupá-las. ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) numera os pedidos de cada cliente do mais recente ao mais antigo; SUM(x) OVER (ORDER BY day) é um total acumulado; LAG(x) OVER (...) lê a linha anterior.
Para filtrar por uma função de janela, o BigQuery oferece QUALIFY, que é executado depois que as janelas são calculadas:
SELECT customer_id, order_id, order_date
FROM shop.orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) = 1;
Isso mantém o último pedido de cada cliente: é a forma padrão de eliminar duplicatas. Se duas linhas empatam na ordenação, acrescente um critério de desempate, aqui order_id, para que o resultado não mude de uma execução para outra. Já RANK e DENSE_RANK mantêm os empates.
Cada produto classificado por preço dentro do seu tipo de torra.