platform, concat(symbol_in,'/',symbol_out) as pair, pool_name

from avalanche.core.ez_dex_swaps

where (AMOUNT_IN_USD between AMOUNT_OUT_USD - 1000 and AMOUNT_OUT_USD + 1000) )

,

final as ( select date, pool_name, count(DISTINCT(platform)) as platforms, count(DISTINCT(pool_name)) as pools, count(DISTINCT(a.tx_hash)) as swaps, sum(volume) as volume_usd, avg(volume) as avg_volume,

median(volume) as median_volume, max(volume) as max_volume, min(volume) as min_volume,

sum(TX_FEE) as total_fee

from volume a join avalanche.core.fact_transactions b on a.tx_hash = b.tx_hash

where date >= '2021-04-01'

and origin_from_address = lower('{{User_Address}}')

group by 1,2)

,

price as ( select HOUR::date as date, avg(price) as avg_price

from ethereum.core.fact_hourly_token_prices

where symbol = 'WAVAX'

and hour::date >= '2021-04-01'

group by 1)

,

final_2 as ( select pool_name, sum(total_fee) as fee_in_Avax, sum(total_fee*avg_price) as fee_in_usd

from final a join price b on a.date = b.date

group by 1)

select sum(fee_in_usd) as fee_usd, sum(fee_in_avax) as fee_avax

from final_2