bachiMultiple delegations2
Updated 2021-08-24
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 totalDelegationByDelegator AS(
SELECT
delegator_address,
count(*) as numDelegations
FROM terra.staking
WHERE (action = 'delegate' OR action = 'redelegate')
AND validator_address_label IS NOT NULL
GROUP BY 1
), delegatorsWithMultipleDelegations AS(
SELECT
delegator_address
FROM totalDelegationByDelegator
WHERE numDelegations > 1
), delegationsAddon AS(
SELECT
block_timestamp,
delegator_address,
validator_address_label,
ROW_NUMBER() OVER(PARTITION by delegator_address ORDER BY block_timestamp) AS DuplicateCount
FROM terra.staking
WHERE (action = 'delegate' OR action = 'redelegate')
), maintable AS(
SELECT
delegationsAddon.delegator_address,
validator_address_label,
duplicatecount
FROM delegationsAddon
INNER JOIN delegatorsWithMultipleDelegations
ON delegationsAddon.delegator_address = delegatorsWithMultipleDelegations.delegator_address
WHERE (duplicatecount = 1 OR duplicatecount = 2)
ORDER BY delegationsAddon.delegator_address, duplicatecount
), temptable AS(
SELECT
delegator_address,
validator_address_label as transaction1validator,
lag(validator_address_label) over (partition by delegator_address order by duplicatecount) as
Run a query to Download Data