kaibladeAlgorand Daily Amount Spent on Gas in USD
    Updated 2022-11-14
    WITH algo_price AS
    (SELECT asset_name, price_usd
    FROM algorand.core.ez_price_pool_balances
    WHERE asset_name = 'ALGO'
    AND block_hour::date = CURRENT_DATE()
    ORDER BY block_hour DESC
    LIMIT 1
    ),
    algo_all_trx AS
    (SELECT *, 'ALGO' AS asset
    FROM algorand.core.fact_transaction
    WHERE block_timestamp::date >= CURRENT_DATE() - INTERVAL '30 days'),

    algo_combined_data AS
    (SELECT trx.block_timestamp, trx.tx_sender, trx.fee*price.price_usd AS gas_cost
    FROM algo_all_trx trx
    JOIN algo_price price
    ON trx.asset = price.asset_name)

    SELECT DATE_TRUNC(day, block_timestamp) AS "Days",
    SUM(gas_cost) AS "Daily Spent on Gas in USD"
    FROM algo_combined_data
    GROUP BY "Days"






    Run a query to Download Data