balance as (

select block_number,

sum(balance) as balance,

sum(effective_balance) as effective_balance,

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

sum(case when validator_status in

('exited_unslashed','exited_slashed','withdrawal_possible') then 1 else 0 end) as exited_validators,

sum(case when slashed=TRUE then 1 else 0 end) as slashed,

sum(case when substr("WITHDRAWAL_CREDENTIALS", 1, 4)='0x01' then 1 else 0 end)

as "0x01 Credentials",

sum(case when substr("WITHDRAWAL_CREDENTIALS", 1, 4)='0x00' then 1 else 0 end)

as "0x00 Credentials",

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

from ethereum.beacon_chain.fact_validators

where validator_status in (

'active_ongoing','active_slashed','active_exiting','exited_unslashed','exited_slashed','withdrawal_possible')

group by 1

),

block as (

select block_number,ROW_NUMBER() OVER(ORDER BY block_number) as index from (

select block_number,

block_number-lag(block_number,1) over (order by block_number) as filter

from (

select distinct block_number from

ethereum.beacon_chain.fact_validators

where (filter>5000 or block_number <500000 or block_number=6440398)) ,

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'