date_trunc('day', recorded_at) as date1,

symbol,

avg(price) as "price"

FROM osmosis.core.dim_prices

GROUP BY 1,2

date_trunc('day', hour) as date1,

symbol,

avg(price) as "price"

FROM ethereum.core.fact_hourly_token_prices

GROUP BY 1,2)

SELECT

date_trunc('week', block_timestamp) as date,

CASE WHEN transfer_type LIKE 'IBC_TRANSFER_IN' then (SPLIT(SENDER, '1'))[0] else (SPLIT(RECEIVER, '1'))[0] end as "Blockchain" ,

sum(CASE WHEN transfer_type LIKE 'IBC_TRANSFER_IN' THEN ("price" * amount) / power(10, decimal) ELSE 0 END) as "Volume from AXELAR chain",

sum(CASE WHEN transfer_type LIKE 'IBC_TRANSFER_OUT' THEN ("price" * amount) / power(10, decimal) ELSE 0 END) as "Volume to AXELAR chain"

FROM axelar.core.fact_transfers LEFT outer JOIN t1 ON lower(symbol) LIKE REPLACE(SUBSTRING(CURRENCY, 2), '-wei', '')

AND date1 = date_trunc('day', block_timestamp)

WHERE transfer_type LIKE 'IBC%'

AND (sender LIKE 'axelar%' or receiver LIKE 'axelar%')

GROUP BY 1,2

HAVING not "Blockchain" LIKE 'axelar' AND date > '2022-07-01'

order by 1