ZSaedS2.4 Top Users following copy
Updated 2023-03-15Copy Reference Fork
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 82a63486-4271-4702-a3cb-42405cb9a440
with clean as (select -- remove fail tarnsaction
a.* from near.social.fact_addkey_events a
INNER JOIN near.core.fact_transactions b on a.tx_hash = b.tx_hash and b.tx_status = 'Success'
where a.BLOCK_TIMESTAMP is not null
)
, singin as (-- get first sing in for each user
select min(BLOCK_TIMESTAMP) as date , SIGNER_ID as user from clean
group by user
)
, period as (-- filter users base entred date
select date , user from singin
where date::date between '{{Start_from}}' and '{{End_At}}'
)
, flat as (
with reg as (
select a.*, parse_json(REGEXP_REPLACE(NODE_DATA,'null','"unfollow"')) as data from
near.social.fact_decoded_actions a
INNER join near.core.fact_transactions b on a.tx_hash = b.tx_hash and b.TX_STATUS ='Success'
where NODE ='graph'
and NODE_DATA:follow is not null
)
select
*
from reg,
table(
FLATTEN( INPUT =>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 value !='unfollow'
Run a query to Download Data