MasiTop Pairs Based on Volume
Updated 2024-10-10
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 tb1 as ( select block_timestamp,
tx_id,
blockchain,
pool_name,
ifnull(from_address,native_to_address) as trader,
case when from_asset ilike '%USDT%' then 'USDT'
when from_asset ilike '%link%' then 'link'
when from_asset ilike '%usdc%' then 'USDC'
when from_asset ilike '%/%' then split(from_asset,'/')[1]
when from_asset ilike '%.%' then split(from_asset,'.')[1]
else from_asset end as from_token,
case when to_asset ilike '%USDT%' then 'USDT'
when to_asset ilike '%usdc%' then 'USDC'
when to_asset ilike '%link%' then 'link'
when to_asset ilike '%/%' then split(to_asset,'/')[1]
when to_asset ilike '%.%' then split(to_asset,'.')[1]
else to_asset end as to_token,
case when from_amount_usd >= to_amount_usd then to_amount_usd else from_amount_usd end as amount1,
case when from_amount_usd is null then to_amount_usd else from_amount_usd end as amount2,
case when amount1 is null then amount2 else amount1 end as amount
from thorchain.defi.fact_swaps
where block_timestamp::date >= '2024-01-01'
and affiliate_address in ('td','ti','tr','te')
)
select
concat(from_token,'->',to_token) as pair,
sum(amount) as volume,
avg(amount) as avg_volume
from tb1
group by 1
order by 2 desc
limit 10
QueryRunArchived: QueryRun has been archived