from avalanche.core.fact_hourly_token_prices

where hour::date >= '2023-06-21'

group by 1,2,3 )

,

swaps as ( select block_timestamp::date as date, platform, tx_hash, origin_from_address , pool_name, amount_in, amount_out, token_in , token_out, amount_in_usd, amount_out_usd

from avalanche.core.ez_dex_swaps

where block_timestamp::date >= '2023-06-21')

,

volume as ( select a.date, platform, tx_hash, origin_from_address, pool_name, amount_in*b.avg_price as in_volume, amount_out*c.avg_price as out_volume,amount_in_usd,amount_out_usd

from swaps a left outer join price b on a.date = b.date and a.token_in ilike b.token_address

left outer join price c on a.token_out = c.token_address and a.date = c.date

)

,

final as ( select date, platform, tx_hash, origin_from_address, pool_name, case when in_volume is null then out_volume else in_volume end as volume,

case when amount_in_usd is null then amount_out_usd else amount_in_usd end as volumes

from volume)

,

final_volume as ( select date, platform, tx_hash, origin_from_address, pool_name,volumes, case when volumes is null then volume else volumes end as vol

from final )

,

fnl as (select origin_from_address, count(DISTINCT(tx_hash)) as tx,

count(DISTINCT(pool_name)) as pools, sum(vol) as usd_volume,

avg(vol) as avg_volume, median(vol) as median_volume, max(vol) as max_volume, min(vol) as min_volume

from final_volume

where platform ilike '%uniswap%'

and origin_from_address not in (select address from avalanche.core.dim_labels)

and origin_from_address not in (select token_address from avalanche.core.fact_hourly_token_prices)

and origin_from_address not in (select address from avalanche.core.dim_contracts )

group by 1)

select count(DISTINCT(origin_from_address)) as users,

sum(usd_volume) as volume,

case when usd_volume <= 10 then 'Below $10'

when usd_volume <= 100 then '$10-$100'

when usd_volume <= 1000 then '$100-$1K'