-- ALGORAND --

with algorand as (with tab1 as (select date_trunc('hour',block_timestamp) as hour, sum(fee) as hourly_fee, count(distinct block_id) as hourly_block_count,

count(distinct tx_sender) as hourly_tx_sender_count, count(distinct tx_group_id) as hourly_tx_count

from algorand.core.fact_transaction

where block_timestamp::date>=current_date-30 and block_timestamp::date<>current_date

group by 1

order by 1),

tab2 as (select block_hour as hour, _algo_price

from algorand.core.ez_price_pool_balances

where block_hour::date>=current_date-30 and block_hour::date<>CURRENT_DATE

order by 1)

select tab1.hour as hour, hourly_fee*_algo_price as hourly_fee_usd, hourly_fee as hourly_fee, hourly_block_count,

(hourly_fee*_algo_price)/hourly_block_count as hourly_fee_per_block_usd, hourly_fee/hourly_block_count as hourly_fee_per_block,

hourly_tx_sender_count, (hourly_fee*_algo_price)/hourly_tx_sender_count as hourly_fee_per_user_usd,

hourly_fee/hourly_tx_sender_count as hourly_fee_per_user, hourly_tx_count,

(hourly_fee*_algo_price)/hourly_tx_count as hourly_fee_per_tx_usd, 'ALGORAND' as blockchain

from tab1 left join tab2 on tab1.hour=tab2.HOUR

order by 1),

-- ARBITRUM --

arbitrum as (with tab1 as (select date_trunc('hour',block_timestamp) as hour, sum(tx_fee) as hourly_fee,

count(distinct block_number) as hourly_block_count, count(distinct tx_hash) as hourly_tx_count,

count(distinct from_address) as hourly_tx_sender_count

from arbitrum.core.fact_transactions

where block_timestamp::date>=current_date-30 and block_timestamp::date<>current_date

group by 1

order by 1),

tab2 as (select hour, price

from ethereum.core.fact_hourly_token_prices

where hour::date>=current_date-30 and hour::date<>CURRENT_DATE and symbol='WETH'

order by 1)