count (distinct event_inputs:from::string) as user_count, 'Inflow' as move

from ethereum_core.fact_event_logs

where event_inputs:to::string = '0x3ee18b2214aff97000d974cf647e7c347e8fa585' and contract_address = '0xa693b19d2931d498c5b318df961919bb4aee87a5' --ust

and event_name = 'Transfer'

group by 1), -- UST ETH > Terra

raw as (select date(block_timestamp) as dt, tx_id, msg_value:sender::string as sender,

msg_value:execute_msg:initiate_transfer:recipient_chain as chain, case

when chain = 1 then 'solana'

when chain = 2 then 'ethereum'

when chain = 4 then 'BSC'

when chain = 5 then 'polygon'

else null end as terra_to_chain,

msg_value:execute_msg:initiate_transfer:asset:amount/1e6 as amount,

msg_value:execute_msg:initiate_transfer:asset:info:native_token:denom::string as uusd

from terra.msgs

where msg_value:contract::string = 'terra10nmmwe8r3g99a9newtqa7a75xfgs2e8z87r2sf'

and msg_value:execute_msg:initiate_transfer is not null

and msg_value:execute_msg:initiate_transfer:asset:info:native_token:denom::string = 'uusd'),

outflow as (select dt, sum(amount) as volume, count(distinct tx_id) as tx_count, count(distinct sender) as user_count, 'Outflow' as move

from raw

where terra_to_chain = 'ethereum'

group by 1

order by 1 asc)

select a.dt, a.volume as inflow_vol, a.tx_count as inflow_tx, a.user_count as inflow_unique_user,

(b.volume*-1) as outflow_vol, b.tx_count as outflow_tx, b.user_count as outflow_unique_user, case

when outflow_vol is null then 0

else outflow_vol end as outflow,

inflow_vol + outflow as net_vol,

sum(inflow_vol) OVER(ORDER BY a.dt asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS inflow_cumulative,

sum(b.volume) OVER(ORDER BY a.dt asc ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS outflow_cumulative

from inflow a

left outer join outflow b on a.dt = b.dt