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,