0xHaM-dTotal Staked Luna by Inactive Wallet Address
Updated 2023-11-11
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
›
⌄
-- forked from Inactive Wallet Address(Those who have not Reward Withdrawal Reward Transaction) @ https://flipsidecrypto.xyz/edit/queries/31cd119a-6900-4f37-9c9d-89625538f253
with tb1 as (
SELECT
DISTINCT tx_id
FROM terra.core.fact_msg_attributes_standard
WHERE ATTRIBUTE_VALUE = 'terravaloper1lelhxdzwn9ddecv6sv0kcxj5tguurxnzcfs5wf'
AND MSG_TYPE = 'withdraw_rewards'
)
, filterTb as (
SELECT
TX_ID,
delegator.ATTRIBUTE_VALUE as delegator_address,
split(amount.ATTRIBUTE_VALUE, 'uluna')[0]::float/1e6 as reward
FROM terra.core.fact_msg_attributes_standard delegator
JOIN terra.core.fact_msg_attributes_standard amount
using(BLOCK_TIMESTAMP, TX_ID)
WHERE tx_id in (SELECT* FROM tb1)
AND (delegator.MSG_TYPE = 'coin_received' and delegator.ATTRIBUTE_KEY = 'receiver')
AND (amount.MSG_TYPE = 'coin_received' and amount.ATTRIBUTE_KEY = 'amount')
AND amount.ATTRIBUTE_VALUE ilike '%luna'
)
, rewardeTb as (
SELECT
delegator_address,
PROJECT_NAME,
count(DISTINCT tx_id) as n_collect_rewards,
sum(reward) as rewards_amount
FROM filterTb
LEFT JOIN terra.core.dim_address_labels on delegator_address = address
GROUP by 1,2
ORDER by 3 DESC
)
, delegations as (
select
DELEGATOR_ADDRESS,
Run a query to Download Data