HosseinUntitled Query
    Updated 2022-07-18
    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