select

date_trunc(day,hour) as date,

avg(price) as Btc_Price

from bitcoin.price.ez_prices_hourly

where 1=1

and hour >= current_date - 30

group by 1

),

main_tab as (

select

date_trunc(day, block_timestamp) as date,

sum(BLOCK_REWARD * Btc_Price) as "BLOCK REWARD",

sum(FEES * Btc_Price) as "Fees Reward",

sum(TOTAL_REWARD * Btc_Price) as "TOTAL REWARD"

from bitcoin.gov.ez_miner_rewards a join price b on date_trunc(day, block_timestamp) = b.date

where block_timestamp >= current_date - 30

group by 1

order by 1 desc

select

sum("BLOCK REWARD") as "Cum BLOCK REWARD",

sum("Fees Reward") as "Cum Fees Reward",

sum("TOTAL REWARD") as "Cum TOTAL REWARD"

from main_tab