adriaparcerisasOsmos staking: since June 2021
Updated 2022-05-31
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
table1 as (
SELECT
block_timestamp,
case when REGEXP_SUBSTR(attribute_value,'i.*') is not null then REGEXP_SUBSTR(attribute_value,'i.*')
else REGEXP_SUBSTR(attribute_value,'u.*') end as attribute_name,
REPLACE(attribute_value, attribute_name, '')::decimal/pow(10,6) as amount_staked
--attribute_value/pow(10,6)
from osmosis.core.fact_msg_attributes
where block_timestamp>='2021-06-01' and msg_type in ('delegate','superfluid_increase_delegation')
and attribute_key in ('new_shares','amount')
),
staking as (
SELECT
trunc(block_timestamp,'day') as date,
sum(amount_staked) as osmo_staked,
sum(osmo_staked) over (order by date) as cum_osmo_staked
from table1
group by 1
),
table2 as (
SELECT
block_timestamp,
case when REGEXP_SUBSTR(attribute_value,'i.*') is not null then REGEXP_SUBSTR(attribute_value,'i.*')
else REGEXP_SUBSTR(attribute_value,'u.*') end as attribute_name,
REPLACE(attribute_value, attribute_name, '')::decimal/pow(10,6) as amount_unstaked
--attribute_value/pow(10,6)
from osmosis.core.fact_msg_attributes
where block_timestamp>='2021-06-01' and msg_type in ('unbond')
and attribute_key in ('new_shares','amount')
),
unstaking as (
SELECT
trunc(block_timestamp,'day') as date,
sum(amount_unstaked) as osmo_unstaked,
sum(osmo_unstaked) over (order by date) as cum_osmo_unstaked
Run a query to Download Data