WITH PRICE as (
SELECT
Currency,
avg(price_usd) as avg_usd_price
from osmosis.mars.ez_token_day
GROUP BY 1
)
SELECT
CASE when
from_symbol > to_symbol
then concat(to_symbol,'/',from_symbol)
else
concat(from_symbol,'/',to_symbol)
end AS LP_Pair,
count(DISTINCT tx_id) as total_transactions,
count(DISTINCT trader) as users,
sum(from_amount*avg_usd_price) as usd_volume
from osmosis.mars.ez_swaps
join price on currency=from_currency
where tx_succeeded = 'TRUE'
group by 1
order by 4 DESC
LIMIT 10