CASE WHEN a.platform IN ('v2.ref-finance.near', 'token.v2.ref-finance.near', 'xtoken.ref-finance.near') THEN 'ref-finance'

WHEN a.platform IN ('fbrrr.jumbo_exchange.near','token.jumbo_exchange.near','v1.jumbo_exchange.near') THEN 'jumbo'

END dex,

COUNT(DISTINCT a.trader) as n_traders,

COUNT(DISTINCT a.tx_hash) as n_swap,

sum(n_swap)over(partition by dex order by date) as cum_n_swap,

sum(a.amount_in*ZEROIFNULL(b.price_usd)) as vol,

avg(a.amount_in*ZEROIFNULL(b.price_usd)) as avg_vol,

count(DISTINCT pool_id) as n_pools

(SELECT *

FROM near.core.ez_dex_swaps a

WHERE platform IN ('v2.ref-finance.near', 'token.v2.ref-finance.near', 'xtoken.ref-finance.near', 'fbrrr.jumbo_exchange.near','token.jumbo_exchange.near','v1.jumbo_exchange.near')

AND amount_in > 0

LEFT JOIN near.core.fact_prices b

ON token_in_contract = token_contract AND date_trunc('day',a.block_timestamp) = date_trunc('day',b.timestamp)

GROUP BY dex,date

ORDER BY date DESC