from osmosis.core.fact_swaps

group by 2)

,

new_osmo as ( select DISTINCT trader

from osmo_new

where date >= CURRENT_DATE - 30)

,

ava_new as ( select min(block_timestamp::date) as date ,ORIGIN_FROM_ADDRESS as trader

from avalanche.sushi.ez_swaps

group by 2)

,

new_ava as ( select DISTINCT trader

from ava_new

where date >= CURRENT_DATE - 30)

,

wavax as ( select 'Osmosis' as type, date(block_timestamp) as date, trader, tx_id, from_amount/pow(10,18) as amounts

FROM osmosis.core.fact_swaps

where from_currency ='ibc/6F62F01D913E3FFE472A38C78235B8F021B511BC6596ADFF02615C8F83D3B373'

and date >= '2023-01-22'

select 'Avalanche' as type, date(block_timestamp) as date, ORIGIN_FROM_ADDRESS as trader, tx_hash as tx_id, amount_in as amounts

from avalanche.sushi.ez_swaps

where symbol_in = 'WAVAX'

and date >= '2023-01-22')

select 'New Users' as status, type, count(DISTINCT(trader)) as swapper, count(DISTINCT(tx_id)) as swaps, sum(amounts) as amount,

avg(amounts) as avg_amount , median(amounts) as median_amount

from wavax

where (trader in ( select trader from new_osmo) or trader in (select trader from new_ava ))

and date <= CURRENT_DATE - 1

group by 1,2

select 'Old Users' as status, type, count(DISTINCT(trader)) as swapper, count(DISTINCT(tx_id)) as swaps, sum(amounts) as amount,

avg(amounts) as avg_amount , median(amounts) as median_amount