elsina✅ 1. Transactions: single numbers - average
Updated 2023-01-12
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
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
with price as (
select
date_trunc('day', recorded_at) as date,
avg(price) as osmo_usd
from osmosis.core.dim_prices
where
symbol = 'OSMO'
group by 1
),
info_tx as (
select
date_trunc('day', block_timestamp) as "Day",
count(distinct tx_id) as "tx count",
count(distinct tx_from) as "Active users",
-- sum((transaction_fee/1e24) * near_usd) as "fee",
-- avg((transaction_fee/1e24) * near_usd) as "Avg tx fee",
(sum(iff(tx_status = 'SUCCEEDED', 1, 0)) / "tx count") * 100 as "Success Rate"
from osmosis.core.fact_transactions
where "Day" >= current_date - 90
group by 1
),
info_fee as (
select
date_trunc('day', block_timestamp) as "Day",
sum((substring (fee,0,charindex('uosmo',fee)-1)/pow(10,6)) * osmo_usd) as "fee",
avg((substring (fee,0,charindex('uosmo',fee)-1)/pow(10,6)) * osmo_usd) as "Avg tx fee"
from osmosis.core.fact_transactions join price on block_timestamp::date = date
where
fee like '%uosmo%' and
"Day" >= current_date - 90
group by 1
),
avg_info as (
select
avg("tx count") as "AVG tx count",
avg("Success Rate") as "AVG success rate",
Run a query to Download Data