adriaparcerisascosmos naka
Updated 2023-01-12
999
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
›
⌄
--select x.* from solana.core.fact_transfers x
--where x.tx_id='5sj8o3pCSpT5vRzGCkt4pp41SesouGZbix8vjFeezr1PfrQakuLPgzj7CdsLqj3QBPmstMSPFrzDQsJahT7JjCjW'
--and index like '%4%'
WITH
stakes1 as (
SELECT
x.block_timestamp,
x.tx_id,
y.attribute_value as validator,
case when REGEXP_SUBSTR(x.attribute_value,'i.*') is not null then REGEXP_SUBSTR(x.attribute_value,'i.*')
else REGEXP_SUBSTR(x.attribute_value,'u.*') end as attribute_name,
REPLACE(x.attribute_value, attribute_name, '')::decimal/pow(10,6)*(-1) as staked
FROM cosmos.core.fact_msg_attributes x
join cosmos.core.fact_msg_attributes y on x.tx_id=y.tx_id
where x.msg_type='unbond' and x.attribute_key='amount' and y.msg_type='unbond' and y.attribute_key='validator'
having staked is not null and attribute_name is not null and staked>-1e9
),
stakes2 as (
SELECT
x.block_timestamp,
x.tx_id,
y.attribute_value as validator,
case when REGEXP_SUBSTR(x.attribute_value,'i.*') is not null then REGEXP_SUBSTR(x.attribute_value,'i.*')
else REGEXP_SUBSTR(x.attribute_value,'u.*') end as attribute_name,
REPLACE(x.attribute_value, attribute_name, '')::decimal/pow(10,6) as staked
FROM cosmos.core.fact_msg_attributes x
join cosmos.core.fact_msg_attributes y on x.tx_id=y.tx_id
where x.msg_type='delegate' and x.attribute_key='amount' and y.msg_type='delegate' and y.attribute_key='validator'
having staked is not null and attribute_name is not null and staked<1e9
),
stakes3 as (
SELECT
x.block_timestamp,
Run a query to Download Data