SELECT

date_trunc('day', hour) AS Date,

avg(price) AS price

FROM

optimism.price.ez_prices_hourly

WHERE

symbol ILIKE 'WETH'

GROUP BY

Date

),

DexSwapsWithPrice AS (

SELECT

block_timestamp,

tx_hash,

origin_from_address,

amount_in_usd,

tx_fee * price AS tx_fee_usd,

CASE

WHEN platform ILIKE 'uniswap%' THEN 'Uniswap'

WHEN platform ILIKE 'velodrome%' THEN 'Velodrome'

WHEN platform ILIKE 'kyberswap%' THEN 'Kyberswap'

WHEN platform ILIKE 'beethoven%' THEN 'Beethoven'

WHEN platform ILIKE 'hashflow%' THEN 'Hashflow'

WHEN platform ILIKE 'dodo%' THEN 'Dodo'

ELSE INITCAP(platform)

END AS platform

FROM

optimism.defi.ez_dex_swaps

JOIN

optimism.core.fact_transactions USING (tx_hash)

JOIN

Prices P ON date_trunc('day', block_timestamp) = P.Date