platform AS "Platform"

, Count(*) AS "Transactions"

, Count(DISTINCT origin_from_address) AS "Swappers"

, COUNT(DISTINCT pool_name) AS "Pairs"

, 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"

, COUNT(DISTINCT symbol) AS "Assets"

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 1

ORDER BY 2 DESC