select price, date_trunc('minute',RECORDED_AT) as time

from osmosis.core.dim_prices

where symbol='OSMO'),

fee_data as (

select

date_trunc('minute',block_timestamp) as time,

fee,

tx_id,

gas_used,

gas_wanted,

case

when fee like '%ibc%' then split(fee,'ibc')[0]

when fee like '%uosmo%' then split(fee,'uosmo')[0]

else split(fee,'uion')[0]

end as uosmo,

uosmo/pow(10,6) as osmo_fee

from osmosis.core.fact_transactions)

select

date_trunc('day',fee_data.time) as date,

count(tx_id) as number_of_txns,

sum(fee_data.osmo_fee*prices.price) as fees

from fee_data

left join prices

on fee_data.time = prices.time

group by 1

order by 1