(

select date_trunc('hour',recorded_hour) as time, (close) as price

from solana.core.fact_token_prices_hourly

where symbol='SOL'

and provider='coinmarketcap'

),

sol_fee_table as

(

select

'solana' as chain,

ft.BLOCK_ID as block_number,

ft.block_timestamp,

ft.tx_id as tx_hash,

ft.signers[0] as from_address,

'0x' as to_address,

ft.fee/10e8 as tx_fee,

p.price,

(tx_fee*p.price) as tx_fee_usd,

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

else 'FAIL'

end as status

from solana.core.fact_transactions ft

inner join sol_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='5mHCzZUjByCtq3FHPEsQwqsScvEANhtigbo9zGienDR8GuQfHPoGKs9P8QJkkwApauvJL7Dp517xqtAewKGRQof3'

)

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,

sum(tx_fee_usd)/count(distinct block_number) as average_fee_per_block