SocioCryptodapp
Updated 2023-05-30
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 CEX @ https://flipsidecrypto.xyz/edit/queries/8cfb13ab-b48e-41b0-83cb-91f706ca03dd
with combined as (
SELECT
project_name,
block_timestamp::date as date,
from_address as users,
avax_value as value,
tx_hash
FROM avalanche.core.fact_transactions a
LEFT JOIN avalanche.core.dim_labels b
on a.to_address = b.address
WHERE status = 'SUCCESS' and label_type = 'dapp'
UNION
SELECT
project_name,
block_timestamp::date as date,
to_address as users,
-1*avax_value as value,
tx_hash
FROM avalanche.core.fact_transactions a
LEFT JOIN avalanche.core.dim_labels b
on a.from_address = b.address
WHERE status = 'SUCCESS' and label_type = 'dapp'
)
SELECT date_trunc('{{interval}}',date) as date,
project_name,
count(DISTINCT users) as n_users,
sum(value) as total_net_value,
count(DISTINCT tx_hash) as n_txns
FROM combined
GROUP BY 1,2
Run a query to Download Data