Kruys-Collinsdefeated-copper
Updated 2024-09-16
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 delegator_metrics AS (
SELECT
delegator_address,
SUM(CASE WHEN action = 'delegate' AND tx_succeeded = TRUE THEN amount/1e6 ELSE 0 END) AS total_staked_amount,
SUM(CASE WHEN action = 'undelegate' AND tx_succeeded = TRUE THEN -amount/1e6 ELSE 0 END) AS total_undelegated_amount,
SUM(CASE WHEN action = 'redelegate' AND tx_succeeded = TRUE THEN amount/1e6 ELSE 0 END) AS total_redelegated_amount,
COUNT(*) AS total_transactions,
COUNT(DISTINCT validator_address) AS unique_validators
FROM
axelar.gov.fact_staking
GROUP BY
delegator_address
),
net_staked AS (
SELECT
delegator_address,
total_staked_amount + total_undelegated_amount AS net_staked_amount
FROM
delegator_metrics
),
total_net_staked AS (
SELECT
SUM(net_staked_amount) AS total_net_staked_amount
FROM
net_staked
),
average_transactions AS (
SELECT
AVG(total_transactions) AS avg_transactions_per_delegator
FROM
delegator_metrics
)
SELECT
dm.delegator_address,
dm.total_staked_amount as "Total Staked Amount $AXL",
dm.total_undelegated_amount as "Total Unstaked Amount $AXL",