where method_name in ('deposit_and_stake')),

df as (

SELECT

date_trunc('day', block_timestamp) as date,

tx_receiver as validator,

regexp_substr(tx:receipt[0]:outcome:logs, 'Contract total staked balance is\\W+\\w+') as stake_balance,

try_to_numeric(trim(REGEXP_REPLACE(stake_balance, '[a-z/-/A-z/./#/*"]', ''))) as amount

FROM near.core.fact_transactions join deposit_and_stake_events on deposit_and_stake_events.tx_hash=near.core.fact_transactions.tx_hash)

select count (DISTINCT validator), round(sum (stake),2),

min(stake) as min_pool,

avg(stake) as AVG_pool,

max(stake) as max_pool,

median(stake) as median_pool

from (

replace(validator, '.near') as validator,

avg(amount)/pow(10,24) as Stake

FROM df

WHERE date >= CURRENT_DATE - {{Period}} and amount is not null

group by 1

order by 2 desc )