hessMonthly Transfers
Updated 2023-05-03
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
›
⌄
with transaction as ( select trunc(block_timestamp,'month') as date, 'Outflow' as type, tx_hash,tx_signer as user,project_name, deposit/pow(10,24) as near
from near.core.fact_transfers a join near.core.dim_address_labels b on a.tx_receiver = b.address
where block_timestamp::date >= '2022-01-01'
and label_type = 'cex' and tx_signer not in (select DISTINCT address from near.core.dim_address_labels
where label_type = 'cex')
UNION
select trunc(block_timestamp,'month') as date, 'Inflow' as type, tx_hash, tx_receiver as user,project_name, deposit/pow(10,24) as near
from near.core.fact_transfers a join near.core.dim_address_labels b on a.tx_signer = b.address
where block_timestamp::date >= '2022-01-01'
and label_type = 'cex' and tx_receiver not in (select DISTINCT address from near.core.dim_address_labels
where label_type = 'cex'))
,
final as ( select date, type, count(DISTINCT(user)) as users, count(DISTINCT(tx_hash)) as total_tx,
sum(near) as near_amount, avg(near) as avg_near, median(near) as median_near,
max(near) as max_near, min(near) as min_near,
sum(total_tx) over (order by date asc) as cum_tx,
sum(near_amount) over (partition by type order by date asc) as cum_near
from transaction
group by 1,2)
,
near_price as ( select trunc(TIMESTAMP,'month') as date, median(price_usd) as near_price
from near.core.fact_prices
where symbol = 'STNEAR'
group by 1)
select a.date, *
from final a join near_price b on a.date = b.date
Run a query to Download Data