'Osmosis' as blockchain,

block_timestamp::date as day,

sum(split(fee,'uosmo')[0]::numeric/pow(10,6)) * avg(price) as Fee_in_usd,

sum(Fee_in_usd) over (order by day) as cum_fee

from osmosis.core.fact_transactions inner join osmosis.core.dim_prices p

on RECORDED_AT::date = block_timestamp::date and hour(p.RECORDED_AT) = hour(block_timestamp)

where block_timestamp > CURRENT_DATE - 31

and tx_status = 'SUCCEEDED'

and symbol = 'OSMO'

and fee ilike '%uosmo%'

group by day

select

'Solana' as blockchain,

block_timestamp::date as day,

(sum(fee/pow(10,9)) * avg(close)) as Fee_in_usd,

sum(Fee_in_usd) over (order by day) as cum_fee

from solana.core.fact_transactions inner join solana.core.fact_token_prices_hourly p

on RECORDED_HOUR::date = block_timestamp::date and hour(p.RECORDED_HOUR) = hour(block_timestamp)

where block_timestamp > CURRENT_DATE - 31

and symbol = 'SOL'

and succeeded = 'TRUE'

group by day

select

'Flow' as blockchain,

block_timestamp::date as day,

(sum(gas_limit/pow(10,9)) * avg(price_usd)) as Fee_in_usd,

sum(Fee_in_usd) over (order by day) as cum_fee

from flow.core.fact_transactions inner join flow.core.fact_prices p