tx_hash,

origin_from_address,

pool_name,

symbol_in,

symbol_out,

case when amount_in_usd is null then amount_out_usd

when amount_out_usd is null then amount_in_usd

when amount_in_usd>=amount_out_usd then amount_in_usd

when amount_out_usd>amount_in_usd then amount_out_usd end as amount_usd

from avalanche.defi.ez_dex_swaps

where platform LIKE '%pharaoh%'

and block_timestamp::date >= '2025-01-01'

,

tbl1 as (select

symbol_in as token,

'Sell' as type,

sum(amount_usd) as volume,

count(DISTINCT tx_hash) as swaps

from pharaoh

WHERE amount_usd is not null

group by 1,2

order by 3 desc

limit 20)

,

tbl2 as (select

symbol_out as token,

'Buy' as type,

sum(amount_usd) as volume,

count(DISTINCT tx_hash) as swaps

from pharaoh

WHERE amount_usd is not null

group by 1,2

order by 3 desc

limit 20)