nitsNEAR transfers decentralised?
Updated 2022-11-10
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
›
⌄
with signers as
(SELECT date(block_timestamp) as day, count(DISTINCT tx_signer) as total_signers, sum(total_signers) over (order by day) as cum_signers, ceil(total_signers/1000) as top_signers, count(tx_hash) as total_txs
from near.core.fact_transfers
GROUP by 1 )
SELECT day, sum(total_txs_per_signer) as total_txs_by_top, avg(total_signers) as net_signers,
avg(total_txs) as net_txs, total_txs_by_top/net_txs*100 as percent_top_txs
from
(SELECT *, row_number() over (partition by day order by total_txs_per_signer desc) as rn
from
(SELECT date(block_timestamp) as day_,tx_signer, count(DISTINCT tx_hash) as total_txs_per_signer
from near.core.fact_transfers
GROUP by 1, 2 )
inner join signers
on day = day_ )
where rn < top_signers
GROUP by 1
-- SELECT * from signers
-- limit 100
Run a query to Download Data