Popular Routes for Whales

Eman-RazPopular Routes for Whales

Updated 2024-04-01

with final_table as (with avax as (with tab1 as (select date_trunc('day',block_timestamp) as "📅Date", tx_hash as swap, ORIGIN_FROM_ADDRESS AS "👩‍💻Trader",

symbol_in as "🔴Token from", symbol_out as "🟢Token to",

sum(amount_in_usd) as volume, CASE

when sum(amount_in_usd)<=1 then 'V<=1 USD'

WHEN sum(amount_in_usd)>1 AND sum(amount_in_usd)<=10 THEN '1<V<=10 USD'

WHEN sum(amount_in_usd)>10 AND sum(amount_in_usd)<=100 THEN '10<V<=100 USD'

WHEN sum(amount_in_usd)>100 AND sum(amount_in_usd)<=1000 THEN '100<V<=1K USD'

WHEN sum(amount_in_usd)>1000 AND sum(amount_in_usd)<=10000 THEN '1K<V<=10K USD'

WHEN sum(amount_in_usd)>10000 AND sum(amount_in_usd)<=100000 THEN '10K<V<=100K USD'

WHEN sum(amount_in_usd)>100000 AND sum(amount_in_usd)<=1000000 THEN '100K<V<=1M USD'

WHEN sum(amount_in_usd)>1000000 THEN 'V>1M USD'

END AS "Classification"

from avalanche.defi.ez_dex_swaps

where (platform='trader-joe-v1' or platform='trader-joe-v2') and block_timestamp::date>='{{Start_Date}}' and block_timestamp::date<='{{End_Date}}'

and amount_in_usd>0

group by 1,2,3,4,5)

select "📅Date", swap AS "🔗Swap TX", volume as "💰Volume (USD)", "👩‍💻Trader", "🔴Token from", "🟢Token to", 'Avalanche' as "Chain"

from tab1

where "Classification"='V>1M USD'

order by 3 desc),

----------------------------------------------------------------------------------------------------------

arb as (with tab1 as (select date_trunc('day',block_timestamp) as "📅Date", tx_hash as swap, ORIGIN_FROM_ADDRESS AS "👩‍💻Trader",