kaibladeNear Social Stake Stats
Updated 2023-03-29
999
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
WITH
social_inits as (
select * from near.core.fact_actions_events_addkey
where receiver_id = 'social.near'
),
add_signer as (
select
k.tx_hash,
k.block_id,
k.block_timestamp,
t.tx_signer,
k.allowance,
t.tx_signer = t.tx_receiver
from social_inits k
left join near.core.fact_transactions t using (tx_hash)
),
de_dupe_resigners as (
select
block_timestamp,
tx_signer
from add_signer
WHERE block_timestamp::date BETWEEN '{{Start_Date}}' AND '{{End_Date}}'
qualify ROW_NUMBER() over (
PARTITION BY tx_signer
ORDER BY
block_timestamp
) = 1
),
stake_actions_count AS
(SELECT DISTINCT(tx_signer) AS stakers, COUNT(tx_hash) AS stake_count
FROM near.core.dim_staking_actions
WHERE tx_signer IN (SELECT tx_signer FROM de_dupe_resigners)
-- AND action= 'Stake'
GROUP BY stakers),
Run a query to Download Data