winnie-fsThe time period when users unstake their OSMOs copy
Updated 2024-09-19
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
›
⌄
-- forked from mhm / The time period when users unstake their OSMOs @ https://flipsidecrypto.xyz/mhm/q/WdsNRpDzlfV4/the-time-period-when-users-unstake-their-osmos
with delegates as (
select block_timestamp::date as delegate_date, tx_id, DELEGATOR_ADDRESS, validator_address, label as validator_name, amount/pow(10,decimal) as real_amount
from osmosis.gov.fact_staking s left join osmosis.core.dim_labels l on s.validator_address = l.address
where action = 'delegate'
and TX_SUCCEEDED = 'TRUE'
), undelegates as (
select block_timestamp::date as undelegate_date, tx_id, DELEGATOR_ADDRESS, validator_address, label as validator_name, amount/pow(10,decimal) as real_amount
from osmosis.gov.fact_staking s left join osmosis.core.dim_labels l on s.validator_address = l.address
where action = 'undelegate'
and TX_SUCCEEDED = 'TRUE'
), diff as (
select d.DELEGATOR_ADDRESS, DATEDIFF(day, delegate_date, undelegate_date)::int as duration
from delegates d left join undelegates u on (d.DELEGATOR_ADDRESS=u.DELEGATOR_ADDRESS and d.validator_address=u.validator_address)
where duration >= 0
and undelegate_date > delegate_date
and duration is not null
)
select duration, count(*) as "Count", count(distinct DELEGATOR_ADDRESS) as "Unique Delegators"
from diff
group by 1
order by 1
QueryRunArchived: QueryRun has been archived