with avax_price as (

select

date_trunc('{{interval}}', hour) as days,

median(price) as avax_price

from

avalanche.price.ez_prices_hourly

where

symbol = 'WAVAX'

group by 1

)

SELECT date, sum(CASE when event_name = 'Submitted' then vol_stake

else -1* vol_stake end) as net_staked_vol,

sum(CASE when event_name = 'Submitted' then vol_stake_usd

else -1* vol_stake_usd end) as net_staked_vol_usd

FROM (

SELECT event_name,

date_trunc('{{interval}}', a.block_timestamp) as date,

count(DISTINCT origin_from_address) as n_stakers,

sum(decoded_log:avaxAmount) / pow(10, 18) as vol_stake,

sum(vol_stake)over(partition by event_name order by date) as cum_vol_stake,

sum(decoded_log:avaxAmount * avax_price)/pow(10,18) as vol_stake_usd,

sum(vol_stake_usd)over(partition by event_name order by date) as cum_vol_stake_usd

avalanche.core.ez_decoded_event_logs a

LEFT JOIN avax_price b

ON date_trunc('day', a.block_timestamp) = b.days

event_name = 'Submitted'

AND tx_status = 'SUCCESS'

AND contract_name = 'Staked AVAX'

GROUP by 1,2

SELECT event_name,

date_trunc('{{interval}}', a.block_timestamp) as date,