tubaecciProfits 3
    Updated 2025-01-06
    -- forked from Profits 2 @ https://flipsidecrypto.xyz/studio/queries/08b8e543-9558-4498-9c62-be4abeecda3a

    -- forked from Profits @ https://flipsidecrypto.xyz/studio/queries/ea81bd2b-e51d-4a31-83bf-c442f88d32e4

    WITH revenues AS(
    WITH optimism AS(
    WITH op AS(
    SELECT
    block_timestamp,
    to_address,
    tx_hash,
    tx_fee
    FROM optimism.core.fact_transactions
    WHERE block_timestamp >= TIMESTAMP '2024-01-01'
    )
    SELECT
    DATE_TRUNC ('{{period}}', block_timestamp) AS date,
    'optimism' AS chain,
    ROUND(SUM(tx_fee), 0) AS revenue_eth,
    ROUND(SUM(tx_fee * price), 0) AS revenue_usd,
    COUNT(*) AS transactions
    FROM op AS a
    LEFT JOIN optimism.price.ez_prices_hourly AS p ON DATE_TRUNC('hour', block_timestamp) = p.hour AND symbol = 'ETH'
    GROUP BY 1
    )
    SELECT *
    FROM optimism
    ),
    costs AS(
    WITH optimism AS(
    WITH op AS(
    SELECT
    block_timestamp,
    to_address,
    tx_hash,
    l1_fee