Maditotal_burnt_usd
    Updated 2023-05-13
    WITH gas_eth AS (
    WITH gas_us AS(
    SELECT
    BLOCK_TIMESTAMP,
    GAS_USED as GAS_USED,
    BLOCK_HEADER_JSON : base_fee_per_gas :: INTEGER AS base_fee_per_gas
    FROM ethereum.core.fact_blocks
    )
    SELECT
    date_trunc('days', BLOCK_TIMESTAMP) AS date,
    ROUND(SUM(GAS_USED*base_fee_per_gas/ 1e18), 2) as burnt_gas
    FROM gas_us
    GROUP BY date
    ORDER BY date DESC),

    ethusd AS (
    SELECT
    date_trunc ('day', BLOCK_TIMESTAMP) as date,
    AVG(AMOUNT_USD/AMOUNT) as eth_to_usd
    FROM ethereum.core.ez_eth_transfers
    GROUP BY date
    ORDER BY date DESC)

    SELECT
    -- a.date as date,
    -- a.burnt_gas as burnt_gas_eth,
    sum(round(a.burnt_gas*b.eth_to_usd,2)) as total_gas_usd
    FROM gas_eth a
    LEFT JOIN ethusd b
    ON a.date = b.date
    --ORDER BY date DESC
    Run a query to Download Data