count(distinct tx_sender) as hourly_tx_sender_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_algo, 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_algo,

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_algo

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

order by 1