with

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 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

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())