SELECT

date_trunc('day', hour) AS date,

avg(price) AS avg_price

FROM

ethereum.price.ez_prices_hourly

WHERE

symbol ILIKE 'weth'

GROUP BY

date

),

DexSwapsWithPrice AS (

SELECT

ds.block_timestamp,

ds.tx_hash,

.origin_from_address,

ds.amount_in_usd,

ds.tx_fee * ap.avg_price AS tx_fee_usd,

CASE

WHEN ds.platform ILIKE 'uniswap%' THEN 'Uniswap'

WHEN ds.platform ILIKE 'pancakeswap%' THEN 'Pancakeswap'

WHEN ds.platform ILIKE 'kyberswap%' THEN 'Kyberswap'

WHEN ds.platform ILIKE 'trader-joe%' THEN 'Trader Joe'

WHEN ds.platform ILIKE 'dodo%' THEN 'Dodo'

WHEN ds.platform ILIKE 'hashflow%' THEN 'Hashflow'

ELSE INITCAP(ds.platform)

END AS platform

FROM

ethereum.defi.ez_dex_swaps ds

JOIN

ethereum.core.fact_transactions tx ON ds.tx_hash = tx.tx_hash

JOIN

Prices p ON date_trunc('day', tx.block_timestamp) = p.date