with validators_per_slot as

(

select

slot_number,

validator_status,

count(distinct pubkey) as validators,

sum(effective_balance) as eth_staked

from ethereum.beacon_chain.fact_validators

where validator_status='active_ongoing'

group by slot_number, validator_status

)

select

fb.slot_timestamp::date as date,

val.validators as daily_active_validators,

val.eth_staked,

val.eth_staked / daily_active_validators as avg_eth_staked

from

validators_per_slot val

inner join

ethereum.beacon_chain.fact_blocks fb

on val.slot_number=fb.slot_number

where (date>='{{Start_date}}') and (date<='{{End_date}}')

qualify row_number() over (partition by date order by val.slot_number desc ) = 1

order by date desc