(LEFT(POOL_ADDRESS, CHARINDEX('.', POOL_ADDRESS) - 1)) as validator,

round(sum(stake_amount/pow(10,24))) as "Staked amount, NEAR",

count(DISTINCT tx_hash) as "Number of stakes", count(DISTINCT tx_signer) as "Number of delegators"

from near.core.dim_staking_actions

where action = 'Stake' and block_timestamp >= DATEADD({{Period}}, -{{ago}}, CAST(GETDATE() AS date)) and block_timestamp!= CURRENT_DATE

group by 1

),

dt as (

select max(block_timestamp::date) as max_date from near.core.fact_blocks

),

total_suppl as (

select max(header:total_supply)/pow(10,24) as total_supply from near.core.fact_blocks where

block_timestamp::date = (select max_date from dt)

select

round((select * from total_suppl)) as total_supply,

sum("Staked amount, NEAR") as "Staked amount",

median("Staked amount, NEAR") as "median Staked amount",

round(avg("Staked amount, NEAR"),2) as "avg Staked amount",

round(median("Staked amount, NEAR"), 2)as "median Staked amount",

round(min("Staked amount, NEAR"),2) as "min Staked amount",

count (DISTINCT validator) as Validators,

round(sum("Number of delegators")/Validators,2) as avg_deleg

from df where "Staked amount, NEAR" >0