drone-mostafaplatforms
Updated 2023-05-22
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
›
⌄
with base as (SELECT
date_trunc ('month',BLOCK_TIMESTAMP) as date,
concat ('CEX: ',t3.LABEL, ' => ','DEX: ',t2.LABEL) as type,
count (DISTINCT TX_HASH) as TXN,
count (DISTINCT ORIGIN_FROM_ADDRESS) as Users,
sum (AMOUNT_USD) as USD,
sum (TXN) over (partition by type order by date) as Cum_TXN,
sum (Users) over (partition by type order by date) as Cum_Users,
sum (USD) over (partition by type order by date) as Cum_USD
FROM ethereum.core.ez_token_transfers t1
LEFT JOIN ethereum.core.dim_labels t2 on t2.ADDRESS = t1.TO_ADDRESS
LEFT JOIN ethereum.core.dim_labels t3 on t3.ADDRESS = t1.FROM_ADDRESS
WHERE t3.LABEL_TYPE = 'cex' and t2.LABEL_TYPE = 'dex' -- from cex to dex
and t1.CONTRACT_ADDRESS = '0x1f9840a85d5af5bf1d1762f925bdaddc4201f984' --UNI
and AMOUNT_USD > 0
GROUP BY 1,2
UNION
SELECT
date_trunc ('month',BLOCK_TIMESTAMP) as date,
concat ('DEX: ',t3.LABEL, ' => ','CEX: ',t2.LABEL) as type,
count (DISTINCT TX_HASH) as TXN,
count (DISTINCT ORIGIN_FROM_ADDRESS) as Users,
sum (AMOUNT_USD) as USD,
sum (TXN) over (partition by type order by date) as Cum_TXN,
sum (Users) over (partition by type order by date) as Cum_Users,
sum (USD) over (partition by type order by date) as Cum_USD
FROM ethereum.core.ez_token_transfers t1
LEFT JOIN ethereum.core.dim_labels t2 on t2.ADDRESS = t1.TO_ADDRESS
Run a query to Download Data