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,

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

Osmo_price

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%'

date,

count(distinct TX_ID) as "# TXs",

sum(Gas_USD) as "Gas Amount-USD",

sum("Gas Amount-USD") over(order by date) as "Total Amount USD of Gas",

percentile_cont(0.1) within group(order by Gas_USD) as "%10",

percentile_cont(0.5) within group(order by Gas_USD) as "%50",

percentile_cont(0.9) within group(order by Gas_USD) as "%90",

avg(Gas_USD) as "AVG Gas Amount-USD",

avg(Osmo_price) as Osmo_price

from gas

group by 1

order by date asc