-- forked from Weekly Arbitrum Fees Percent Payed to Ethereum for Blob Transactions copy copy @ https://flipsidecrypto.xyz/studio/queries/de5d5bc3-586a-43a7-aff1-184e4ef88fdc
WITH eth_prices AS (
SELECT
date(hour) as day,
median(price) as eth_price
FROM
ethereum.price.ez_prices_hourly
WHERE
token_address LIKE lower('0xC02aaA39b223FE8D0A0e5C4F27eAD9083C756Cc2')
GROUP BY 1
),
chain_metrics AS (
SELECT
'Blast' as chain,
date_trunc('week', block_timestamp) as day,
sum(tx_fee) as L2_fees_eth,
sum(tx_fee * eth_price) as L2_fees_usd
FROM
blast.core.fact_transactions
LEFT OUTER JOIN eth_prices ON eth_prices.day = date(block_timestamp)
WHERE
block_timestamp > '2022-03-13'
GROUP BY 1, 2
UNION ALL
SELECT
'Base' as chain,
date_trunc('week', block_timestamp) as day,
sum(tx_fee) as L2_fees_eth,
sum(tx_fee * eth_price) as L2_fees_usd
FROM
base.core.fact_transactions
LEFT OUTER JOIN eth_prices ON eth_prices.day = date(block_timestamp)
WHERE