keshanOsmosis Unstakers 1
Updated 2022-07-18
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
›
⌄
⌄
/*
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,