alitaslimiAlgorand Heatmap
    Updated 2022-11-16
    WITH
    algorand AS (
    SELECT
    DATE_TRUNC(hour, block_timestamp) AS hour,
    SUM(fee * price) AS fees,
    AVG(fee * price) AS fees_average
    FROM
    algorand.core.fact_transaction transactions
    JOIN (
    SELECT
    block_hour::date AS day,
    AVG(price_usd) AS price
    FROM
    algorand.core.ez_price_pool_balances
    WHERE
    block_hour::date BETWEEN (CURRENT_DATE - 31) AND (CURRENT_DATE - 1)
    GROUP BY
    day
    ) prices
    ON
    block_timestamp::date = prices.day
    WHERE
    block_timestamp::date BETWEEN (CURRENT_DATE - 31) AND (CURRENT_DATE - 1)
    GROUP BY
    hour
    ),
    arbitrum AS (
    SELECT
    DATE_TRUNC(hour, block_timestamp) AS hour,
    SUM(tx_fee * price) AS fees,
    AVG(tx_fee * price) AS fees_average
    FROM
    arbitrum.core.fact_transactions transactions
    JOIN (
    SELECT
    hour::date AS day,
    Run a query to Download Data