messariNEAR Social Actions by Type
Updated 2023-07-26
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 Eman-Raz / Number of Action By Type Over time @ https://flipsidecrypto.xyz/Eman-Raz/q/56N-N6o37nUv/number-of-action-by-type-over-time
---------------------------------------Post-------------------------------------------
with tab1 as (select DATE_TRUNC('{{Time_Frame}}',block_timestamp) as "Date", count(distinct signer_id) as "User Count",
count(distinct tx_hash) as "Action Count", 'Post' as "Action Type"
from near.social.fact_decoded_actions
where node='index' and try_parse_json(node_data:post) is not null
group by 1),
-------------------------------------poke-------------------------------------------
tab2 as (select DATE_TRUNC('{{Time_Frame}}',block_timestamp) as "Date", count(distinct signer_id) as "User Count",
count(distinct tx_hash) as "Action Count", 'Poke' as "Action Type"
FROM near.social.fact_decoded_actions
WHERE node = 'index' and try_parse_json(node_data:graph)['key']::string = 'poke'
group by 1),
-------------------------------------Widget Deployments------------------------------------
tab3 as (select DATE_TRUNC('{{Time_Frame}}',block_timestamp) as "Date", count(distinct signer_id) as "User Count",
count(distinct tx_hash) as "Action Count", 'Widget Deployments' as "Action Type"
FROM near.social.fact_widget_deployments
group by 1),
-------------------------------------Like---------------------------------------------
tab4 as (select DATE_TRUNC('{{Time_Frame}}',block_timestamp) as "Date", count(distinct signer_id) as "User Count",
count(distinct tx_hash) as "Action Count", 'Like' as "Action Type"
from near.social.fact_decoded_actions
where node='index' and try_parse_json(node_data:like) is not null
group by 1),
-----------------------------------Comment--------------------------------------------
tab5 as (select DATE_TRUNC('{{Time_Frame}}',block_timestamp) as "Date", count(distinct signer_id) as "User Count",
count(distinct tx_hash) as "Action Count", 'Comment' as "Action Type"
from near.social.fact_decoded_actions
where node='index' and try_parse_json(node_data:comment) is not null
group by 1),
Run a query to Download Data