jgvfNEAR . NEAR Centralized Exchange(CEX) Flows - over time based on flow copy
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 saeedmzn / NEAR . NEAR Centralized Exchange(CEX) Flows - over time based on flow @ https://flipsidecrypto.xyz/saeedmzn/q/qFS8d0RFHseg/near-.-near-centralized-exchange-cex-flows---over-time-based-on-flow
with CEXes as (
select ADDRESS , PROJECT_NAME
from near.core.dim_address_labels
where LABEL_TYPE = 'cex'
) ,
Transfer_from_CEXes_tx as (
select BLOCK_TIMESTAMP ::date date ,
tx_hash ,
TX_RECEIVER user,
TX_SIGNER,
(DEPOSIT/1e24) amount ,
PROJECT_NAME CEX
from near.core.fact_transfers join CEXes on TX_SIGNER = ADDRESS
where BLOCK_TIMESTAMP ::date >= '2023-01-01'
) ,
Transfer_to_CEXes_tx as (
select BLOCK_TIMESTAMP ::date date ,
tx_hash ,
TX_RECEIVER,
TX_SIGNER user ,
(DEPOSIT/1e24) amount ,
PROJECT_NAME CEX
from near.core.fact_transfers join CEXes on TX_RECEIVER = ADDRESS
where BLOCK_TIMESTAMP ::date >= '2023-01-01'
),
all_ as (
select 'Outflow' flow , * from Transfer_from_CEXes_tx
UNION
select 'Inflow' flow , * from Transfer_to_CEXes_tx
)
select date_trunc(week,date)::date weekly ,
flow ,
count (DISTINCT user) num_users ,
count (tx_hash) num_transactions ,
Run a query to Download Data