Ethereum As(

select

'Ethereum' as label ,

(sum(t.gas_used/pow(10,9)) * avg(p.price) / count(distinct block_number) ) as results ,

to_date(t.block_timestamp) as dates

from ethereum.core.fact_transactions t , ethereum.core.fact_hourly_token_prices p

where to_date(p.hour) = to_date(t.block_timestamp)

and hour(p.hour) = hour(t.block_timestamp)

and to_date(t.block_timestamp) > current_date - 31

and p.token_address is null

and status='SUCCESS'

group by dates),

Optimism As (

select

'Optimism' as label ,

(sum(t.gas_used/pow(10,9)) * avg(p.price) / count(distinct block_number) ) as results,

to_date(t.block_timestamp) as dates

from optimism.core.fact_transactions t ,optimism.core.fact_hourly_token_prices p

where to_date(p.hour) = to_date(t.block_timestamp)

and hour(p.hour) = hour(t.block_timestamp)

and to_date(t.block_timestamp) > current_date - 31

and p.symbol='OP'

and status='SUCCESS'

group by dates),

Algorand As (

'Algorand' as label ,

(sum(t.fee) * avg(p.price_usd) / count(distinct block_id) ) as results,

to_date(t.block_timestamp) as dates

from algorand.core.fact_transaction t , algorand.core.ez_price_pool_balances p

where to_date(p.block_hour) = to_date(t.block_timestamp)

and hour(p.block_hour) = hour(t.block_timestamp)

and to_date(t.block_timestamp) > current_date - 31

and p.asset_id=0