(

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

block_timestamp::date as "date",

sum(tx_fee_usd) as daily_tx_fee_usd,

avg(price),

sum(daily_tx_fee_usd) over(order by "date" asc) as cum_tx_fee_usd,

sum(tx_fee_usd)/avg(price) as usd_spent_on_gas_to_token_value,