SocioCryptoNet Flow dapp
Updated 2023-10-12
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 Net Flow @ https://flipsidecrypto.xyz/edit/queries/ad676f87-d7e7-47be-bb6e-7acb07a9c1dc
-- send and recieve into dapp in 6 past month
with project_m as (
SELECT
coalesce(x.labels, y.labels) as project,
coalesce(x.flow_in,0) as flow_in,
coalesce(y.flow_out,0) as flow_out
FROM
( SELECT
b.project_name as labels,
sum(CASE WHEN b.label_type = 'dapp' THEN amount_usd end) as flow_in
FROM avalanche.core.ez_avax_transfers a
LEFT JOIN avalanche.core.dim_labels b
ON a.avax_from_address = b.address
WHERE a.block_timestamp > dateadd('month', -{{last_n_month}} ,current_date)
AND b.project_name IS NOT NULL
GROUP BY 1
UNION
SELECT
b.project_name as labels,
sum(CASE WHEN b.label_type = 'dapp' THEN amount_usd end) as flow_in
FROM avalanche.core.ez_token_transfers a
LEFT JOIN avalanche.core.dim_labels b
ON a.from_address = b.address
WHERE a.block_timestamp > dateadd('month', -{{last_n_month}} ,current_date)
AND b.project_name IS NOT NULL
GROUP BY 1
) x
LEFT JOIN
( SELECT
b.project_name as labels,
sum(CASE WHEN b.label_type = 'dapp' THEN amount_usd end)as flow_out
FROM avalanche.core.ez_avax_transfers a
LEFT JOIN avalanche.core.dim_labels b
ON a.avax_to_address = b.address
Run a query to Download Data