Rodolfo-LimaAVG Gas Fee TX - Optimism
    Updated 2022-09-27
    WITH TX_GAS AS (
    SELECT
    HOUR_TIME::DATE AS DATE,
    --BLOCK_TIMESTAMP::MINUTE AS MINUTE,
    AVG(GAS_PRICE) AS AVG_GAS_PRICE, --IN GWEI
    AVG(GAS_LIMIT) AS AVG_GAS_LIMIT, -- UNIT OF GAS
    AVG(GAS_USED) AS AVG_GAS_USED, -- UNIT OF GAS
    AVG(TX_FEE) AS AVG_TX_FEE, -- ETH VALUE
    AVG(TX_FEE_USD) AS AVG_TX_FEE_USD --USD VALUE
    FROM
    (SELECT
    DATE_TRUNC('hour',TX.BLOCK_TIMESTAMP) AS HOUR_TIME,
    TX.TX_HASH,
    TX.GAS_PRICE,
    TX.GAS_USED,
    TX.GAS_LIMIT,
    TX.TX_FEE,
    TX.STATUS,
    P.PRICE,
    P.PRICE * TX.TX_FEE AS TX_FEE_USD
    FROM
    optimism.core.fact_transactions TX
    INNER JOIN ethereum.core.fact_hourly_token_prices P ON DATE_TRUNC('hour',TX.BLOCK_TIMESTAMP) = P.HOUR
    WHERE P.SYMBOL = 'WETH'
    ORDER BY 1)
    WHERE TX_HASH IS NOT NULL
    AND STATUS = 'SUCCESS'
    GROUP BY 1
    ORDER BY 1 ASC)

    SELECT
    GAS.DATE,
    D.DAY_OF_WEEK_NAME,
    -- Gas price
    AVG_GAS_PRICE, --IN GWEI
    AVG_GAS_LIMIT, -- UNIT OF GAS
    Run a query to Download Data