Flipside Team3rd CEX Inflow/Outflow
Updated 2024-09-25
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 3rd CEX Inflow/Outflow copy copy @ https://flipsidecrypto.xyz/edit/queries/694b1d08-2f57-4217-8e57-579af5e54e1f
-- fork from https://flipsidecrypto.xyz/adriaparcerisas/near-cex-flows-Dk5vaE
WITH
active_receivers as (
select
trunc(x.block_timestamp,'day') as date,
project_name as cex,
count(distinct tx_receiver) as n_users,
count(distinct x.tx_hash) as txs,
sum(deposit/pow(10,24)) as volume
from near.core.fact_transfers x
join near.core.dim_address_labels y
on tx_signer=address and label_type='cex' and label_subtype='hot_wallet'
where block_timestamp>='{{starting_date}}'
group by 1,2),
active_senders as (
select
trunc(x.block_timestamp,'day') as date,
project_name as cex,
count(distinct tx_signer) as n_users,
count(distinct x.tx_hash) as txs,
sum(deposit/pow(10,24))as volume
from near.core.fact_transfers x
join near.core.dim_address_labels y
on tx_receiver=address and label_type='cex' and label_subtype='hot_wallet'
where block_timestamp>='{{starting_date}}'
group by 1,2),
results as (
select
ifnull(x.date,y.date) as date,
ifnull(x.cex,y.cex) as cex,
ifnull(x.n_users,0) as senders_to_cex,
ifnull(y.n_users,0) as receivers_from_cex,