abaOsmosis - daily total gas(usd) last 30 days
Updated 2022-11-16
99
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
›
⌄
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