balance as (

select block_number,

ROW_NUMBER() OVER(ORDER BY block_number asc) as index,

sum(balance) as balance,

sum(effective_balance) as effective_balance,

sum(case when validator_status = 'active_ongoing' then 1 else 0 end) as validator_count,

sum(case when balance>60 then 1 else 0 end) as double_deposit

from ethereum.beacon_chain.fact_validators

group by 1

),

price as (

select date_trunc('day',hour) as day,avg(price) as price

from crosschain.core.ez_hourly_prices

where symbol = 'WETH'

and blockchain = 'ethereum'

and hour>=date('2022-09-15')

group by 1

),

day as (

select date_day as day,

ROW_NUMBER() OVER(ORDER BY date_day asc) as index

from ethereum.core.dim_dates

where date_day>=date('2022-09-15')

and date_day<=date(current_date())

select a.day,reward,

reward-lag(reward,1) over (order by a.day) as daily_reward,

(reward-lag(reward,1) over (order by a.day))*price as daily_reward_usd,

validator_count,

daily_reward/validator_count as avg_daily_reward,

daily_reward/validator_count*price as avg_daily_reward_usd,

(daily_reward/validator_count)/32*365*100 as apy