with base as (select

*,

row_number() over (partition by ORIGIN_FROM_ADDRESS order by BLOCK_TIMESTAMP) as n

FROM ethereum.core.ez_dex_swaps

WHERE PLATFORM IN ('uniswap-v2', 'uniswap-v3')

and

AMOUNT_OUT_USD is not null

and

abs(AMOUNT_OUT_USD-AMOUNT_IN_USD) < 200

select

date_trunc(Month ,BLOCK_TIMESTAMP) :: date as date ,

iff(n=1,'NEW USER','OTHER') as type ,

count(distinct ORIGIN_FROM_ADDRESS) as users ,

sum(users)over(order by date) as cum_users ,

count(distinct tx_hash) as swaps ,

sum(swaps)over(order by date) as cum_swaps ,

sum (AMOUNT_OUT_USD) as swap_amount,

sum(swap_amount)over(order by date) as cum_swap_amount ,

avg (AMOUNT_OUT_USD) as avg_swap_amount ,

swap_amount/users as usd_per_swapper

from

base

where

date > current_date - interval '12 months'

group by 1,2