with

t1 as (

select trunc(block_timestamp,'day') as date,

sum(raw_amount/pow(10,8)) as volume_transferred,

count(distinct origin_from_address) as active_transferers

from avalanche.core.fact_token_transfers

where block_timestamp>=current_date - 30 and contract_address=lower('0x152b9d0fdc40c096757f570a51e494bd4b943e50')

group by 1

),

t2 as (

trunc(hour,'day') as date,

avg(price) as eth_price,

LAG(eth_price,1) IGNORE NULLS OVER (ORDER BY date) as last_price,

((eth_price-last_price)/eth_price)*100 as eth_price_change

from ethereum.core.fact_hourly_token_prices where symbol='WAVAX' and hour>=current_date - 30

group by 1

),

t4 as (

select

t1.date,

volume_transferred,active_transferers, eth_price as avax_price,eth_price_change as avax_price_change

from t1

join t2 on t1.date=t2.date

select * from t4

order by 1 asc