SELECT t.*,

split(CURRENCY, ',') as aa

from terra.core.ez_transfers t

where CURRENCY like '%uluna%'

and TX_SUCCEEDED = 'TRUE'

),

raw2 as (

SELECT

f.value as aa2,

*,

REGEXP_SUBSTR(aa2, $$[^\d*][\S+]*$$) AS input_ibc,

CAST(REGEXP_SUBSTR(aa2, $$^\d*$$) AS bigint) AS input_amount,

case when currency = 'uluna' and CAST(amount AS bigint) > 0 then (CAST(amount AS bigint) ) when INPUT_IBC = 'uluna' and INPUT_AMOUNT > 0 then try_to_number(INPUT_AMOUNT) end as main_amount

from raw1, table(flatten(aa)) f

where INPUT_IBC = 'uluna'

),

raw3 as (

SELECT

sender as address,

(main_amount)/1e6 * -1 as flow

FROM raw2

union all

select

receiver as address,

(main_amount)/1e6 as flow

FROM raw2

),

wallets as (

select

address ,

sum(flow) as holdings

from raw3

group by address