brian-terraTalis Stader Delegations
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 airdrop_plus as (
SELECT
TRUNC(block_timestamp, 'day') as adate,
SUM(CASE event_type
WHEN 'delegate' THEN NVL(event_attributes:amount[0]:amount, event_attributes:amount)
WHEN 'unbond' THEN -NVL(event_attributes:amount[0]:amount, event_attributes:amount)
WHEN 'redelegate' THEN (
CASE WHEN event_attributes:source_validator = 'terravaloper1qd0uk3wrw73x662y2gx4kaulrzlcky6275gl5s' THEN -NVL(event_attributes:amount[0]:amount, event_attributes:amount)
WHEN event_attributes:destination_validator = 'terravaloper1qd0uk3wrw73x662y2gx4kaulrzlcky6275gl5s' THEN NVL(event_attributes:amount[0]:amount, event_attributes:amount)
ELSE 0
END
)
END) / 1e6 as delegated
FROM terra.msg_events
WHERE
(event_type = 'unbond' OR event_type = 'delegate' OR event_type = 'redelegate')
AND tx_id IN (
SELECT DISTINCT
tx_id
FROM terra.msgs
WHERE
(
msg_value:contract = 'terra1r2vv8cyt0scyxymktyfuudqs3lgtypk72w6m3m' -- airdrop+ staking
--OR msg_value:contract = 'terra1xacqx447msqp46qmv8k2sq6v5jh9fdj37az898' -- liquid luna staking
)
AND tx_status = 'SUCCEEDED'
)
AND block_timestamp >= '2021-11-01'
AND (
event_attributes:source_validator = 'terravaloper1qd0uk3wrw73x662y2gx4kaulrzlcky6275gl5s'
OR event_attributes:destination_validator = 'terravaloper1qd0uk3wrw73x662y2gx4kaulrzlcky6275gl5s'
OR event_attributes:validator = 'terravaloper1qd0uk3wrw73x662y2gx4kaulrzlcky6275gl5s'
)
AND tx_status = 'SUCCEEDED'
GROUP BY adate
),
Run a query to Download Data