pinehearstOsmosis Staking
Updated 2023-02-24Copy 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
25
26
27
28
29
30
31
32
33
34
35
36
›
⌄
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