MLDZMNtopic3
Updated 2023-11-19
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
›
⌄
-- forked from topic1 @ https://flipsidecrypto.xyz/edit/queries/49b20497-e541-42bd-bf1f-4175d3c09d52
--Thanks to rmas for decoding like actions
with t1 as ( SELECT a.tx_hash
, a.block_timestamp
, (CASE WHEN replace(split(a.node_data[k.value]::string, '":')[1], '}', '')::string = 'null'
THEN 'UNFOLLOW' ELSE 'FOLLOW' END) AS action
, a.signer_id AS follower_account_id
, f.value::string AS followed_account_id
FROM near.social.fact_decoded_actions AS a
, LATERAL FLATTEN (input => (CASE WHEN is_object(a.node_data) THEN object_keys(a.node_data) ELSE NULL END)) AS k
, LATERAL FLATTEN (input => object_keys(try_parse_json(a.node_data[k.value]))) AS f
WHERE a.node = 'graph'
AND k.value = 'follow'
and action='FOLLOW'
)
select
followed_account_id as "Followed user",
count(distinct follower_account_id) as "Followers",
count(distinct tx_hash) as "Follows"
from t1
group by 1
order by 2 desc limit 10
Run a query to Download Data