vals as (

select trunc(debut,'day') as date,

count (distinct validator) as new_validators,

sum(new_validators) over (order by date) as cum_validators

from (select pubkey as Validator,min(slot_timestamp) as debut from ethereum.beacon_chain.fact_deposits group by 1)

group by 1

),

deposits as (

select

trunc(slot_timestamp,'day') as date,

count(distinct slot_number) as n_deposits,

count(distinct pubkey) as n_validators,

sum(deposit_amount) as deposited_amount,

avg(deposit_amount) as avg_deposited_amount

from ethereum.beacon_chain.fact_deposits

group by 1

order by 1 asc

)

x.date,

new_validators,

cum_validators,

avg(new_validators) over (order by x.date rows between 6 preceding and current row) as avg_7d_ma_new_validators,

n_deposits,

n_validators,

deposited_amount,

avg_deposited_amount,

avg(deposited_amount) over (order by x.date rows between 6 preceding and current row) as avg_7d_ma_deposited_amt

from vals x join deposits y on x.date=y.date

order by 1 asc