hour::date AS timespan,

symbol,

avg (price) AS price

FROM ethereum.price.ez_prices_hourly

WHERE symbol IN ('WETH', 'WAVAX', 'WMATIC')

GROUP BY 1, 2

),

base_table AS (

DISTINCT t.tx_hash,

'Arbitrum' AS chain,

CASE

WHEN platform LIKE 'uniswap%' THEN 'Uniswap'

ELSE 'Others'

END AS type,

CASE

WHEN platform LIKE 'uniswap%' THEN 'uniswap'

WHEN platform LIKE 'dodo%' THEN 'dodo'

WHEN platform LIKE 'kyberswap%' THEN 'kyberswap'

WHEN platform LIKE 'trader-joe%' THEN 'trader-joe'

WHEN platform LIKE 'pancakeswap%' THEN 'pancakeswap'

WHEN platform LIKE 'hashflow%' THEN 'hashflow'

WHEN platform LIKE 'camelot%' THEN 'camelot'

WHEN platform LIKE 'zyberswap%' THEN 'zyberswap'

WHEN platform LIKE 'ramses%' THEN 'ramses'

WHEN platform LIKE 'quickswap%' THEN 'quickswap'

WHEN platform LIKE 'velodrome%' THEN 'velodrome'

ELSE platform

END AS protocol,

platform,

t.block_timestamp,

sender,

amount_in_usd,