pinehearstOsmosis Staking
    Updated 2023-02-24
    with osmosis_staking AS (
    SELECT
    *,
    case when action = 'delegate' then block_timestamp end as stake_date,
    case when action = 'undelegate' then block_timestamp end as unstake_date,
    case when action = 'delegate' then amount/pow(10,decimal) end as osmos_staked,
    case when action = 'undelegate' then amount/pow(10,decimal) end as osmos_unstaked
    FROM osmosis.core.fact_staking
    WHERE tx_succeeded = TRUE AND currency = 'uosmo'
    ),
    stakers AS (
    SELECT
    delegator_address,
    min(date(stake_date))as first_stake,
    max(date(stake_date))as last_stake,
    min(date(unstake_date))as first_unstake,
    max(date(unstake_date))as last_unstake,
    count(osmos_staked) as staking_count,
    sum(osmos_staked) as staked,
    avg(osmos_staked) as avg_staked,
    median(osmos_staked) as median_staked,
    count(osmos_unstaked) as unstaking_count,
    sum(osmos_unstaked) as unstaked,
    staked - ifnull(unstaked, 0) as net_staked
    FROM osmosis_staking
    GROUP BY 1
    ),
    status_tier AS (
    SELECT
    case when staked <= 1000 then 'a. Shrimp <1k'
    when staked > pow(10,3) AND staked <= pow(10,4) then 'b. Crab 1-10k'
    when staked > pow(10,4) AND staked <= pow(10,5) then 'c. Fish 10-100k'
    when staked > pow(10,5) AND staked <= pow(10,6) then 'd. Shark 100k-1M'
    when staked > pow(10,6) then 'e. Whale >1M' end as amount_tier,
    case when net_staked < 0 and unstaked is not null then 'Unstaked All'
    when net_staked < 1 then '< 1 Osmos Staked Currently'
    Run a query to Download Data