keshanOsmosis Unstakers 1
    Updated 2022-07-18
    /*
    1. Find undelgations
    2. Find the next transactions each user did after completing the undelegation
    3. Find what is the nature of that transaction
    */
    WITH undelegators AS (
    SELECT
    tx_id AS tid,
    block_timestamp,
    delegator_address,
    validator_address,
    label AS validator_name,
    amount / pow(10, decimal) AS amnt,
    completion_time AS cdate
    FROM osmosis.core.fact_staking
    LEFT JOIN osmosis.core.dim_labels
    ON address=validator_address
    WHERE
    action='undelegate'
    AND tx_status='SUCCEEDED'
    ),
    next_tx AS (
    SELECT
    tx_id,
    un.block_timestamp AS unstake_date,
    un.delegator_address AS user,
    un.validator_address AS validator_address,
    un.validator_name AS validator_name,
    un.amnt, td AS tx_date
    FROM undelegators un
    LEFT JOIN (
    SELECT
    m.tx_id,
    delegator_address,
    m.block_timestamp AS td,
    u.tid AS ud,