avg(price) AS eth_price

FROM ethereum.core.fact_hourly_token_prices

where symbol = 'WETH'

GROUP BY 1)

,fee_opt AS (

SELECT

tx_hash,

tx_fee

FROM optimism.core.fact_transactions

GROUP BY 1,2)

,fee_eth AS (

SELECT

tx_hash,

tx_fee

FROM ethereum.core.fact_transactions

GROUP BY 1,2)

SELECT date_trunc('day', block_timestamp) AS date, 'Optimism' AS blockchain,

COUNT(x.tx_hash) AS n_txs,

COUNT(DISTINCT(ORIGIN_FROM_ADDRESS)) AS n_swapers,

sum(AMOUNT_IN_USD) AS tot_in_usd,

avg(AMOUNT_IN_USD) AS avg_in_usd,

sum(AMOUNT_OUT_USD) AS tot_out_usd,

avg(AMOUNT_OUT_USD) AS avg_out_usd,

sum(AMOUNT_OUT_USD - AMOUNT_IN_USD) AS tot_net_flow,

avg(AMOUNT_OUT_USD - AMOUNT_IN_USD) AS avg_net_flow,

sum(tx_fee) AS tot_eth_fee,

avg(tx_fee) AS avg_eth_fee,

sum(tx_fee*eth_price) as tot_fee_usd,

avg(tx_fee*eth_price) as avg_fee_usd

FROM optimism.sushi.ez_swaps x JOIN price y ON x.block_timestamp::date = y.date