with btc_price as ( select
date_trunc(day,hour) as date,
avg(price) as BTC_price
from ethereum.token_prices_hourly
where
symbol in ('WBTC')
and date_trunc(day,hour) >= CURRENT_DATE - 30
group by 1
),
sushivolume as (
SELECT block_timestamp::date as date,
count (origin_from_address) as num_swappers,
count (distinct tx_hash) as num_swaps,
sum(amount_in_usd) as USD_Volume,
sum (num_swaps) over (order by date) as cum_swaps,
sum (USD_Volume) over (order by date ) as cum_USD_volume
FROM ethereum.sushi.ez_swaps
WHERE BLOCK_TIMESTAMP >= CURRENT_DATE - 30
GROUP BY 1
)
select date ,
BTC_price ,
USD_Volume
from btc_price join sushivolume using (date)
group by 1,2,3