As funções de janela calculam um valor para cada linha a partir de um conjunto de linhas relacionadas, sem agrupá-las: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) numera os pedidos de cada cliente do mais recente para o mais antigo.
Na maioria dos bancos de dados, não dá para filtrar por esse número no WHERE, porque as janelas são calculadas depois do WHERE. Seria preciso envolver a consulta em uma subconsulta. O Snowflake tem o QUALIFY, um filtro que roda depois das funções de janela:
SELECT customer_id, order_id, order_ts
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) = 1;
Essa é a forma padrão de manter a linha mais recente por chave e de remover duplicados de um feed. Se duas linhas empatam na ordenação, o ROW_NUMBER escolhe uma ao acaso, então adicione um critério de desempate ao ORDER BY. Já o RANK mantém os empates.
Os dois produtos mais caros de cada categoria.