(

select date_trunc('hour',recorded_at) as time, avg(price) as price

from osmosis.core.dim_prices

where symbol = 'OSMO'

group by time

),

osmo_fee_table as

(

select

'osmosis' as chain,

ft.block_id as block_number,

ft.block_timestamp,

ft.tx_id as tx_hash,

ft.tx_from as from_address,

'0x' as to_address,

-- regexp_replace(ft.fee,'\D+','')::numeric as tx_fee,

(REPLACE(fee, NVL(REGEXP_SUBSTR(fee,'/d+'), REGEXP_SUBSTR(fee,'u.*')), '')::decimal)/10e6 as tx_fee,

p.price,

(tx_fee*p.price) as tx_fee_usd,

case when ft.TX_STATUS='SUCCEEDED' then 'SUCCESS'

else 'FAIL'

end as status

from osmosis.core.fact_transactions ft

inner join osmo_price p

on date_trunc('hour',ft.block_timestamp)=p.time

where 1=1

and ft.block_timestamp > CURRENT_DATE - interval ' 30 days'

-- and tx_hash='3152E3E2471C530FFB9DF54D06D994578EEE16A96D2780F77FE5C18432FE199D'

)

select

avg(tx_fee_usd) as avg_tx_fee_usd,

count(distinct tx_hash) as total_number_of_transactions,

count(distinct tx_hash)/30 as average_number_of_transaction,

count(distinct block_number) as number_of_blocks,