SELECT

date_trunc('day', hour) as date,

avg(price) as price_eth

FROM ethereum.price.fact_hourly_token_prices

WHERE token_address = LOWER('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')

AND hour >= '2020-01-01'

GROUP BY date

),

arb_df AS (

SELECT

date_trunc('day', hour) as date,

avg(price) as price_arb

FROM arbitrum.price.fact_hourly_token_prices

WHERE token_address = LOWER('0x912CE59144191C1204E64559FE8253a0e49E6548')

GROUP BY date

date_trunc('day', block_timestamp) as date,

count(distinct tx_hash) as transactions,

sum(tx_fee * price_eth) as "ARB fee earned in USD",

sum(tx_fee * price_eth) / count(distinct tx_hash) as "average fee USD",

(sum (GAS_USED_FOR_L1 * price_eth/POWER(10,10))) as fee_to_l1,

("ARB fee earned in USD" - fee_to_l1) as profit,

(avg(arb_df.price_arb) * 10000000000) as fdv_arb

FROM arbitrum.core.fact_transactions arb

JOIN eth_df eth ON date_trunc('day', arb.block_timestamp) = eth.date

JOIN arb_df ON date_trunc('day', arb.block_timestamp) = arb_df.date

WHERE block_timestamp >= '2020-01-01'

GROUP BY 1;