HosseinUntitled Query
Updated 2022-07-18Copy 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
›
⌄
with list as (
select distinct(tx_id), delegator_address, block_timestamp from osmosis.core.fact_staking
where action = 'undelegate'
and tx_status = 'SUCCEEDED'
)
,
list2 as (
select * from (
select a.block_timestamp, a.delegator_address as wallet_address, validator_address,
ROW_NUMBER() OVER (PARTITION BY wallet_address ORDER BY a.block_timestamp ASC) AS n
from osmosis.core.fact_staking a
join osmosis.core.fact_transactions b
join list on list.delegator_address = b.tx_from and a.tx_id = b.tx_id
and a.tx_status = 'SUCCEEDED'
and a.block_timestamp - interval '14 days' >= list.block_timestamp
and a.action = 'redelegate'
) where n = 1
)
select validator_address, count(distinct(wallet_address)) as wallets_num from list2
group by validator_address
order by wallets_num desc
limit 10
Run a query to Download Data