hessTop Platforms
Updated 2023-01-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
›
⌄
with swap_from as ( select date(block_timestamp) as date, origin_from_address ,origin_to_address, tx_hash , amount_in_usd
from ethereum.core.ez_dex_swaps
where symbol_in = 'DYDX' )
,
swap_to as ( select date(block_timestamp) as date, origin_from_address , origin_to_address, tx_hash , amount_out_usd
from ethereum.core.ez_dex_swaps
where symbol_out = 'DYDX' )
,
final as ( select 'Swap To DYDX' as type, origin_to_address, count(DISTINCT(tx_hash)) as total_tx,
sum(amount_out_usd) as volume, avg(amount_out_usd) as avg_usd
from swap_to
where date >= CURRENT_DATE - 180
group by 1,2
UNION
select 'Swap From DYDX' as type,origin_to_address, count(DISTINCT(tx_hash)) as total_tx,
sum(amount_in_usd) as volume, avg(amount_in_usd) as avg_usd
from swap_from
where date >= CURRENT_DATE - 180
group by 1,2)
select type, label , sum(total_tx) as total_swap, sum(volume) as total_volume, avg(avg_usd) as averageg_usd,
rank() over (partition by type order by total_swap desc) as rank
from final a join ethereum.core.dim_labels b on a.origin_to_address = b.address
group by 1 ,2
qualify rank <= 10
Run a query to Download Data