kaibladeNew Authorizers copy
Updated 2023-03-15
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
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
-- forked from 69856e79-1d53-4ae5-9f4f-0a9e4514d888
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
-- ignore subsequent authorizations by the same signer
qualify ROW_NUMBER() over (
PARTITION BY tx_signer
ORDER BY
block_timestamp ASC
) = 1
)
select
block_timestamp::date as "Date",
count(distinct tx_signer) as "Unique Authorizers",
sum("Unique Authorizers") over (order by "Date") as "Cumulative Unique Authorizers"
from de_dupe_resigners
Run a query to Download Data