hessTotal Monthly Number of Validators
Updated 2022-10-20
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
›
⌄
with stake as ( select date(block_timestamp) as date, pool_address, tx_hash, tx_signer, stake_amount/pow(10,24) as raw_amount
from near.core.dim_staking_actions)
,
price as ( select timestamp::date as date, avg(price_usd) as avg_price
from near.core.fact_prices
where symbol ilike '%near%'
group by 1)
,
final as ( select a.date , pool_address, tx_hash, tx_signer, raw_amount, raw_amount*avg_price as stake_usd
from stake a left outer join price b on a.date = b.date )
select trunc(date,'month') as monthly , count(DISTINCT(pool_address)) as total_pool,
count(DISTINCT(tx_signer)) as total_user, sum(raw_amount) as stake_amoumt, count(DISTINCT(tx_hash)) as total_tx,
avg(raw_amount) as avg_stake_amount, median(raw_amount) as median_amount,
sum(stake_usd) as volume,
avg(stake_usd) as avg_volume,
median(stake_usd) as median_stake,
max(stake_usd) as max_usd
from final
group by 1
Run a query to Download Data