Las funciones de ventana calculan un valor para cada fila a partir de un conjunto de filas relacionadas, sin agruparlas en una sola: ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) numera los pedidos de cada cliente del más reciente al más antiguo.
En la mayoría de las bases de datos no puedes filtrar por ese número en WHERE, porque las funciones de ventana se calculan después de WHERE. Tendrías que envolver la consulta en una subconsulta. Snowflake tiene QUALIFY, un filtro que se ejecuta después de las funciones de ventana:
SELECT customer_id, order_id, order_ts
FROM orders
QUALIFY ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_ts DESC) = 1;
Esta es la forma habitual de conservar la fila más reciente por clave y de eliminar duplicados de un flujo. Si dos filas empatan en el orden, ROW_NUMBER elige una al azar, así que añade un criterio de desempate a ORDER BY. En cambio, RANK conserva los empates.
Los dos productos más caros de cada categoría.