with swap as (

select date(block_timestamp) as date, tx_hash, trader, symbol_out, symbol_in , token_in_contract, TOKEN_OUT_CONTRACT, concat(symbol_in,'/',symbol_out) as pair

from near.defi.ez_dex_swaps

where PLATFORM ilike '%ref-finance.near'

and block_timestamp::date >= '2024-01-01'

,

final as ( select date, trader,

count(DISTINCT(tx_hash)) as total_tx,count(DISTINCT(trader)) as total_user

from swap

group by 1 ,2)

select trunc(date,'week') as weekly, count(DISTINCT(trader)) as total_user,

sum(total_tx) as total_txs, avg(total_tx) as avg_tx,

sum(total_txs) over (order by weekly asc) as cum_tx

from final

group by 1