I have a list of Trade class which has fields like buy amount, buy currency, sell amount, sell currency, buy sell flag, settlement date, account, execution date time etc. I also have a lookback period of time in minutes (which I’ll get from the application.yml file). Now, I want to aggregate buy amount by grouping the trades into combination of account, settlement date and buy currency for all trades within execution date time minus the lookback period. Similarly, I want to aggregate sell amount by grouping them into combination of account, settlement date and sell currency for all trades within execution date time minus the lookback period.

In the aggregation process, the buy and sell currency are matched as per the buy sell flag.

While aggregating buy amount, if buy sell flag is B, then match buy currency with buy currency or else with sell currency.

While aggregating sell amount if the buy sell flag is B, then match sell currency with buy currency or else with sell currency.

If nothing matches while grouping, then the aggregated buy or sell amount value will be populated as 0.

Buy and sell amounts are in Bigdecimal, settlement date is in Localdate and execution date time is in Zoneddatetime.

This is the SQL query for reference :-

select

f.business_date,

f.trade_key,

f.account key,

f.currency_pair,

f.execution_date_time,

f.buy_sell_flag,

f.buy_currency,

convert( float, f.buy_amount) as buy_amount,

f.sell_currency,

convert( float, f.sell_amount ) as sell_amount,

f.local_currency,

convert( float, f.local_amount ) as local_amount,

f.region_cd,

isnull (

( select convert (float, sum(isnull( g.local_amount, 0.0 ) ) )

from group g

where f.account_key = g.account_key

and g.buy_currency = case

when f.buy_sell_flag = ‘B’ then f.buy_currency

else f.sell_currency

end

and f.settlement_pay_date = g.settlement_pay_date

and g.buy_sell_flag = ‘B’

and dateadd(mi, -convert (int, lookback_time) , f.execution date_time) < g.execution_date_time

and f.execution_date_time >= g.execution_date_time

) , 0

) as sum_buy_amount,

isnull (

(select convert (float, sum(isnull( g.local_amount, 0.0 ) ) )

from group g

where f.account_key = g.account_key

and g.sell_currency = case

when f.buy_sell_flag = ‘B’ then f.buy_currency

else f.sell_currency

end

and f.settlement_pay_date = g.settlement_pay_date

and f.region_cd = g.region_cd

and g.buy_sell_flag = ‘S’

and dateadd(mi, -convert (int, lookback_time ) , f.execution date_time) < g.execution_date_time

and f.execution_date_time >= g.execution_date_time

), 0

) as sum_sell_amount

f refers to the list of trades

So, how should I go about it. I know we need to use 3 level nested map for this. but the currency matching is making it a little complicated.

Thanks in advance.