ZSaed4.3 like post Top Users unlike
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
›
⌄
-- forked from c4301022-201c-4db5-823a-054b3851e1b7
-- forked from 2ce47619-e00b-4be1-b7c7-3168e148ca06
-- forked from f918d130-e0b2-44c5-b0e2-fc8db53dc576
with only_post as (
select *
from near.social.fact_decoded_actions
,table(FLATTEN( INPUT =>NODE_DATA))
where NODE ='index'
and key = 'like'
)
, data as (
select a.* , a.NODE_DATA:"like" as v
, TRY_PARSE_JSON(VALUE):key:path as pat
, REGEXP_SUBSTR(pat,'(.*)\/post\/(.*)',1 , 1, 'e',1 ) as user_like
, REGEXP_SUBSTR(pat,'(.*)\/post\/(.*)',1 , 1, 'e',2 ) as user_type_like
from only_post a INNER join near.core.fact_transactions b on a.tx_hash = b.tx_hash and b.TX_STATUS ='Success'
where TRY_PARSE_JSON(VALUE):key:type ='social'
)
, like_comment as (
select a.* from data a
INNER join near.core.fact_transactions b on a.tx_hash = b.tx_hash and b.TX_STATUS ='Success'
where user_type_like = 'comment'
)
, like_post as
(select a.* from data a
INNER join near.core.fact_transactions b on a.tx_hash = b.tx_hash and b.TX_STATUS ='Success'
where user_type_like = 'main'
)
select
Run a query to Download Data