SELECT

tx_id,

block_timestamp,

'IBC' as bridge, amount/pow(10, 6) as amount, currency as symbol_address, sender, receiver

, CASE WHEN receiver like 'sei%' THEN receiver ELSE sender END as user

, CASE WHEN receiver like 'sei%' THEN 'in' ELSE 'out' END as cat

, transfer_type

from sei.core.fact_transfers

where 1=1

and (block_timestamp::date >= '2023-08-15' AND block_timestamp<='2023-08-30')

AND ( (receiver like 'sei%' and (sender like 'osmo%' or sender like 'cosmos%'))

OR

(sender like 'sei%' and (receiver like 'osmo%'or receiver like 'cosmos%'))

OR

(sender like 'sei%' and receiver like 'axelar%')

OR

(sender like 'axelar%' and receiver like 'sei%')

)

and TX_SUCCEEDED = 'true'

and transfer_type IN ('IBC_TRANSFER_IN' , 'IBC_TRANSFER_OUT')

and tx_id= 'EBE25926123A54B5ECA7968813F1BE83E3E0579AD145162A65A0F56852329BEB'

,

price_ibc as (

select

trunc(RECORDED_hour,'hour') as hourly

, case

when symbol = 'OSMO' then 'ibc/ED07A3391A112B175915CD8FAF43A2DA8E4790EDE12566649D0C2F97716B8518'

when symbol = 'ATOM' then 'ibc/6CDD4663F2F09CD62285E2D45891FC149A3568E316CE3EBBE201A71A78A69388'

end as token_address, symbol, avg(price) as prices

from osmosis.price.ez_prices

where 1=1

and RECORDED_hour::date >= '2023-08-15'