Select A.col1, sum(A.metric1) over (partition by A.col1, B.col1 order by A.col2 asc) as Cust_col, B.col1 from A JOIN B on (A.join_key=B.join_key) where A.col3='X' QUALIFY ROW_NUMBER () OVER (PARTITION BY A.col1,B.COL1 ORDER BY A.col3 DESC) = 1

Select C.col1,C.cust_col,C.col1,ROW_NUMBER () OVER (PARTITION BY A.col1,C.COL1 ORDER BY C.col3 DESC) as Row_num from (Select A.col1, sum(A.metric1) over (partition by A.col1, B.col1 order by A.col2 asc) as Cust_col,B.col1 from A JOIN B on (A.join_key=B.join_key) where A.col3='X') C where C.ROW_NUM=1