This query worked for me:
with t1 as (
select 1 as CustomerID, date('2022-09-19') as DateOfTransaction, 1 as TransactionNum, 50 as Amount
union all select 1 as CustomerID, date('2022-09-19') as DateOfTransaction, 2 as TransactionNum, 75 as Amount
union all select 2 as CustomerID, date('2022-09-19') as DateOfTransaction, 1 as TransactionNum, 40 as Amount
union all select 1 as CustomerID, date('2022-09-18') as DateOfTransaction, 1 as TransactionNum, 25 as Amount
union all select 2 as CustomerID, date('2022-09-18') as DateOfTransaction, 1 as TransactionNum, 40 as Amount
union all select 2 as CustomerID, date('2022-09-18') as DateOfTransaction, 2 as TransactionNum, 60 as Amount
union all select 2 as CustomerID, date('2022-09-18') as DateOfTransaction, 3 as TransactionNum, 70 as Amount
union all select 3 as CustomerID, date('2022-09-17') as DateOfTransaction, 1 as TransactionNum, 10 as Amount
union all select 4 as CustomerID, date('2022-09-17') as DateOfTransaction, 1 as TransactionNum, 15 as Amount
),
t2 as (
select 3 as CustomerID, date('2022-09-17') as DateOfTransaction, 120 as OverRideAmount
)
select CustomerID, DateOfTransaction, TransactionNum, Amount from
(select *, row_number() over(partition by DateOfTransaction, CustomerID order by Amount desc) rn
from
(select t1.CustomerID, t1.DateOfTransaction, cast(t1.TransactionNum as string) as TransactionNum, t1.Amount
from t1
union all
select t2.CustomerID, t2.DateOfTransaction, 'Manual' as TransactionNum, t2.OverRideAmount as Amount
from t2
)
group by CustomerId, Amount, TransactionNum , DateOfTransaction)
where rn = 1
order by DateOfTransaction desc
Output:
