TRUNC(hour, 'DAY') as DATE_TIME,

symbol,

AVG(PRICE) AS PRICE

FROM crosschain.price.ez_hourly_token_prices

WHERE symbol like Any('WETH', 'MATIC', 'BNB')

GROUP BY 1, 2

TRUNC(t1.block_timestamp, 'WEEK') as WEEKLY,

CASE

when platform ilike '%uniswap%' then 'uniswap'

when platform ilike '%sushiswap%' then 'sushiswap'

when platform ilike '%curve%' then 'curve'

when platform ilike '%balancer%' then 'balancer'

when platform ilike '%hashflow%' then 'hashflow'

when platform ilike '%dodo%' then 'dodo'

END AS SWAP_PROGRAM,

COUNT(DISTINCT t1.tx_hash) as TRANSACTIONS,

COUNT(DISTINCT origin_from_address) as TOTAL_USERS,

SUM(amount_in_usd) as VOLUME_USD,

AVG(amount_in_usd) as VOLUME_USD_AVG,

SUM(tx_fee * PRICES.PRICE) as FEE_USD,

AVG(tx_fee * PRICES.PRICE) as FEE_USD_AVG,

SUM(VOLUME_USD) OVER (PARTITION BY SWAP_PROGRAM ORDER BY WEEKLY) AS VOLUME_CUM

FROM base.defi.ez_dex_swaps t1

JOIN base.core.fact_transactions t2

ON t1.tx_hash = t2.tx_hash

JOIN PRICES ON TRUNC(t1.block_timestamp, 'DAY') = DATE_TIME AND symbol = 'WETH'

WHERE platform ilike Any ('%uniswap%', '%sushiswap%', '%curve%', '%balancer%', '%hashflow%', '%dodo%')

AND status ilike 'success'

AND t1.block_timestamp >= '2023-01-01'

GROUP BY SWAP_PROGRAM, WEEKLY

ORDER BY SWAP_PROGRAM, WEEKLY