select

date_trunc('day', recorded_at) as date,

avg (price) as Osmo_price

from osmosis.core.dim_prices

where symbol = 'OSMO'

group by 1

),

gas as (

select

BLOCK_TIMESTAMP::date as date,

TX_ID,

TX_FROM as from_address,

((split(fee,'uosmo')[0]::numeric)/pow(10,6)) * Osmo_price as Gas_USD

from osmosis.core.fact_transactions a join price b on a.BLOCK_TIMESTAMP::date = b.date

where 1=1

and TX_STATUS = 'SUCCEEDED'

and BLOCK_TIMESTAMP >= CURRENT_DATE - 30

and BLOCK_TIMESTAMP < CURRENT_DATE

and fee ilike '%uosmo%'

and TX_FROM is not null

select Top 10 from_address,

sum(Gas_USD) as Gas_Volume_USD

from gas

group by 1

order by 2 desc