grahamNEAR CEX transactions
Updated 2022-10-25Copy Reference Fork
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 near_prices as (
select
TRUNC(TIMESTAMP,'hour') as timestamp_h,
avg(price_usd) as price_usd
from near.core.fact_prices
where timestamp >= (current_date - {{metric_days}})
AND symbol = 'wNEAR'
group by 1
),
deposits as (
select
a.tx_signer as user_address,
b.project_name as exchange_name,
count(distinct a.tx_hash) as n_deposits,
sum(a.deposit / pow(10,24)) as near_tokens_deposited,
sum(a.deposit / pow(10,24) * c.price_usd) as usd_deposited
from near.core.fact_transfers a
inner join (select distinct address, project_name
from crosschain.core.address_labels
where blockchain = 'near' and label_subtype = 'deposit_wallet') b
on a.tx_receiver = b.address
left join near_prices c
on TRUNC(a.block_timestamp,'hour') = c.timestamp_h
where
block_timestamp >= (current_date - {{metric_days}})
group by 1,2
),
withdraws as (
select tx_receiver as user_address,
project_name as exchange_name,
count(distinct tx_hash) as n_withdraws,
sum(deposit / pow(10,24)) as near_tokens_withdrawn,
sum(a.deposit / pow(10,24) * c.price_usd) as usd_withdrawn
from near.core.fact_transfers a
inner join (select distinct address, project_name
from crosschain.core.address_labels
Run a query to Download Data