Ali3NAverage Hourly Staking/Unstaking Activity on Osmosis December 2022
Updated 2023-01-14Copy Reference Fork
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
›
⌄
with osmopricet as (
select recorded_hour as date1,
price as OSMO_Price
from osmosis.core.ez_prices
where symbol = 'OSMO'
and recorded_hour >= '2022-12-01'),
maintable as (
select date_trunc(hour,block_timestamp) as date,
case when block_timestamp::date = '2022-12-10' then 'December 10'
when block_timestamp::date < '2022-12-10' then 'Previous Days'
when block_timestamp::date > '2022-12-10' then 'Next Days'
end as timespan,
OSMO_Price,
action,
count (distinct tx_id) as TX_Count,
count (distinct delegator_address) as Users_Count,
sum (amount/pow(10,decimal)) as Total_Volume,
avg (amount/pow(10,decimal)) as Average_Volume
from osmosis.core.fact_staking t1 join osmopricet t2 on date_trunc(hour,t1.block_timestamp) = t2.date1
where block_timestamp::Date >= '2022-12-03' and block_timestamp::date < '2022-12-18'
and tx_succeeded = 'TRUE'
and currency = 'uosmo'
group by 1,2,3,4)
select timespan,
action,
avg (tx_count) as average_TX_Count,
avg (users_count) as avrage_users_count,
avg (total_volume) as Average_Volume
from maintable
group by 1,2
Run a query to Download Data