select

date(block_timestamp) as block_date,

currency,

symbol,

avg(price_usd) as avg_price

from terra.oracle_prices

where block_date >= date('2021-01-01')

group by 1, 2, 3

),

sent_across_the_bridge as (

select

date(block_timestamp) as block_date,

'Sent to Ethereum' as transaction_type,

msg_value:amount[0]:denom as original_denom,

substring(msg_value:amount[0]:denom, 2, 6) as dervied_denom,

concat(dervied_denom, ' sent to Ethereum') as derived_transaction_type,

p.avg_price as avg_price_for_currency,

sum(msg_value:amount[0]:amount / POW(10, 6)) * p.avg_price as derived_value_usd

from terra.msgs m

inner join daily_prices p

on date(block_timestamp) = p.block_date and msg_value:amount[0]:denom = p.currency

where msg_value:to_address::string = 'terra13yxhrk08qvdf5zdc9ss5mwsg5sf7zva9xrgwgc'

and block_date >= date('2021-01-01')

and tx_status = 'SUCCEEDED'

and original_denom in ('uluna')

group by 1, 2, 3, 4, 5, 6

),

received_from_bridge as (

select

date(block_timestamp) as block_date,

'Received from Ethereum' as transaction_type,

msg_value:amount[0]:denom as original_denom,

substring(msg_value:amount[0]:denom, 2, 6) as dervied_denom,

concat(dervied_denom, ' received from Ethereum') as derived_transaction_type,

p.avg_price as avg_price_for_currency,