hessTop Pairs Based on Number of Swaps
Updated 2023-04-12
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
›
⌄
with price as ( select timestamp::date as date, a.symbol, b.token_contract,decimals, avg(PRICE_USD) as avg_price
from near.core.fact_prices a join near.core.dim_token_labels b on a.token_contract = b.token_contract
where timestamp::date >= '2023-01-01'
group by 1,2,3,4)
,
swap as ( select date(block_timestamp) as date, tx_hash, trader,
(amount_in)*b.avg_price as volume_in, token_out, token_in , token_in_contract, TOKEN_OUT_CONTRACT,
(amount_out)*c.avg_price as volume_out , concat(token_in,'/',token_out) as pair
from near.core.ez_dex_swaps a join price b on a.block_timestamp::date = b.date
and a.token_in_contract = b.token_contract
join price c on a.TOKEN_OUT_CONTRACT = c.token_contract
and a.block_timestamp::date = c.date
and PLATFORM = 'v2.ref-finance.near'
and block_timestamp::date >= '2023-01-01')
select pair,
count(DISTINCT(tx_hash)) as total_tx,count(DISTINCT(trader)) as total_user,
sum(volume_in) as total_volume, avg(volume_in) as avg_volume,
median(volume_in) as median_volume
from swap
where date >= '2023-01-01'
group by 1
order by 2 desc
limit 5
Run a query to Download Data