MLDZMNtcex3
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 cex1 @ https://flipsidecrypto.xyz/edit/queries/38ea1948-4ef5-4bd7-9733-451855906dcd
with tb1 as(SELECT
*
from near.core.dim_address_labels
where label_type='cex'
),
final as (SELECT
tx_hash,
block_timestamp,
receiver_id AS token_contract,
try_cast(args:amount::string AS bigint) AS raw_amount,
raw_amount / power(10, b.decimals) AS amount,
signer_id AS sender,
args:receiver_id::string AS receiver
FROM near.core.fact_actions_events_function_call a
left join near.core.dim_token_labels b on a.receiver_id=b.TOKEN_CONTRACT
WHERE block_timestamp >= '2022-01-01'
and receiver_id ilike '%usdt%'
AND method_name IN ('ft_transfer', 'ft_transfer_call')
AND raw_amount > 0
)
select
date_trunc('day', BLOCK_TIMESTAMP) as date,
Project_name as CEXs,
count(distinct tx_hash) as no_transfer,
count(distinct sender) as no_sender,
sum(amount) as total_volume,
avg(amount) as avg_volume,
median(amount) as median_volume
from final s left join near.core.dim_address_labels a on s.RECEIVER=a.address
Run a query to Download Data