MasiTop Pairs Based on Volume
    Updated 2024-10-10
    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