HOUR::date as day,

avg(PRICE) as price_token

from ethereum.core.fact_hourly_token_prices where SYMBOL='WETH'

group by 1),

tb2 as (SELECT

date_trunc('day',BLOCK_TIMESTAMP::date) as day,

avg(tx_fee) as avg_fee_ETH,

avg(tx_fee*price_token) as avg_fee_usd

from aurora.core.fact_transactions s

left join tb1 on s.BLOCK_TIMESTAMP::date=tb1.day

where BLOCK_TIMESTAMP>='2022-05-01'

group by 1

),

tb3 as (select

date_trunc('day',BLOCK_TIMESTAMP::date) as date,

count(distinct tx_hash) as no_txn,

no_txn*avg_fee_ETH as saved_ETH,

no_txn*avg_fee_usd as saved_USD

from aurora.core.fact_transactions s

left join tb2 on s.BLOCK_TIMESTAMP::date=tb2.day

where BLOCK_TIMESTAMP>='2022-05-01'

and TX_FEE='0'

group by 1,avg_fee_ETH,avg_fee_usd)

select

sum(saved_ETH) as "Total saved ETH",

sum(saved_USD) as "Total saved USD"

from tb3