platform

, symbol

, Count(*) AS Transactions

, Count(DISTINCT origin_from_address) AS Swappers

, SUM(CASE WHEN amount_in_usd IS NOT NULL AND amount_out_usd IS NOT NULL THEN (amount_in_usd+amount_out_usd)/2

WHEN amount_in_usd IS NULL THEN amount_out_usd ELSE amount_in_usd END) AS volume

FROM (

SELECT

platform

, symbol_in AS symbol

, origin_from_address

, pool_name

, amount_in_usd

, amount_out_usd

FROM avalanche.defi.ez_dex_swaps

WHERE platform LIKE '%pharaoh%'

UNION ALL

SELECT

platform

, symbol_out AS symbol

, origin_from_address

, pool_name

, amount_in_usd

, amount_out_usd

FROM avalanche.defi.ez_dex_swaps

WHERE platform LIKE '%pharaoh%'

GROUP BY 2, 1

HAVING volume > 0

ORDER BY 5 DESC

LIMIT 50