abaOsmosis - daily total gas(usd) last 30 days
    Updated 2022-11-16
    with osmosis_daily_prices as (
    select date(recorded_at) as date,
    avg (price) as price_usd
    from osmosis.core.dim_prices
    where symbol = 'OSMO' and provider = 'coinmarketcap'
    group by 1
    )
    , osmosis_gas_last30days as (
    select date(t.block_timestamp) date
    ,avg(p.price_usd) as avg_price
    ,sum(((split(fee,'uosmo')[0]::numeric)/1e6) ) as gas
    ,sum(((split(fee,'uosmo')[0]::numeric)/1e6) * p.price_usd) gas_usd
    ,avg(((split(fee,'uosmo')[0]::numeric)/1e6) * p.price_usd) avg_gas_usd
    from osmosis.core.fact_transactions t
    join osmosis_daily_prices p on p.date = date(t.block_timestamp)
    where date(t.block_timestamp) > current_date - 30 and fee ilike '%uosmo%'
    group by 1
    )
    select *
    from osmosis_gas_last30days
    order by date



    Run a query to Download Data