10 Dedupe

-- 10_dedupe: Choose the latest version deterministically
-- Read-only reference query. Setup must be loaded first.
WITH ranked AS (
 SELECT batch_row,source_order_id,lower(trim(status_text)) AS status,
 ROW_NUMBER() OVER (PARTITION BY source_order_id ORDER BY loaded_at DESC,batch_row DESC) AS position
 FROM aabha_sql.raw_orders
)
SELECT batch_row,source_order_id,status FROM ranked WHERE position=1 ORDER BY source_order_id;