ZSaed0.4 followers & following count
Updated 2023-10-02
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
›
⌄
with flat as (select
*
from near.social.fact_decoded_actions,
table(
FLATTEN( INPUT =>NODE_DATA:follow)
)
where NODE ='graph'
and NODE_DATA:follow is not null
)
, follow as (
select SIGNER_ID,key, max(BLOCK_TIMESTAMP) as date from flat
where this not like '%null%'
-- and SIGNER_ID= 'manzanal.near'
group by SIGNER_ID,key
)
,unfollow as
(
select SIGNER_ID,key, max(BLOCK_TIMESTAMP) as date from flat
where this ilike '%null%'
-- and SIGNER_ID= 'manzanal.near'
group by SIGNER_ID,key
)
,abslot_unfollow as (
select unfollow.SIGNER_ID,unfollow.key
from unfollow JOIN follow on(unfollow.SIGNER_ID = follow.SIGNER_ID AND
unfollow.key = follow.key and unfollow.date > follow.date
))
, following_count as (
select follow.SIGNER_ID as user,count(DISTINCT follow.key) as num_following
from follow left join abslot_unfollow as b on (
follow.SIGNER_ID = b.SIGNER_ID
and follow.KEY != b.key )
where b.SIGNER_ID is null
group by user
)
Run a query to Download Data