with price as (select date_trunc('day',hour) as day, avg(price) as price FROM crosschain.core.ez_hourly_prices
where symbol = 'WBTC'
and blockchain = 'ethereum'
group by 1
),
btc_vol as (
select date_trunc('day',block_timestamp) as day,
sum(output_value) as btc_volume
from bitcoin.core.fact_transactions
group by 1)
select date_trunc('month', day) as month, sum(usd_btc_volume) as usd_btc_volume,
sum(btc_volume) as btc_volume from (
select a.day, price*btc_volume as usd_btc_volume, btc_volume
from price a
left join btc_vol b
on a.day=b.day)a
group by 1