SocioAnalyticacosmos eligible address
Updated 2024-01-04
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 base as (
SELECT
block_timestamp,
tx_id,
msg_type as action
FROM cosmos.core.fact_msg_attributes
WHERE msg_type IN ('delegate', 'unbond')
),
dd as (
SELECT
a.tx_id ,
split(ATTRIBUTE_VALUE,'/')[0] as delegator_address
FROM cosmos.core.fact_msg_attributes a
JOIN base b ON a.tx_id = b.tx_id
WHERE msg_type = 'tx'
and ATTRIBUTE_KEY = 'acc_seq'
)
,
amnt as (
SELECT
a.tx_id,
split(ATTRIBUTE_VALUE,'uatom')[0] as amount
FROM cosmos.core.fact_msg_attributes a
JOIN base b ON a.tx_id = b.tx_id
WHERE msg_type IN ('delegate', 'unbond')
AND ATTRIBUTE_KEY = 'amount'
AND ATTRIBUTE_VALUE ilike '%uatom'
)
SELECT
delegator_address,
sum(CASE when action = 'delegate' then amount/pow(10,6) end) as amount_delegated,
sum(CASE when action = 'undelegate' then amount/pow(10,6) end) as amount_undelegated,
amount_delegated - amount_undelegated as atom_staked
FROM (
SELECT
QueryRunArchived: QueryRun has been archived