mz0111metrics 6
Updated 2023-01-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
›
⌄
--credit : 0xHaM☰d
--https://app.flipsidecrypto.com/velocity/queries/6b0e2fdc-b788-4018-ba8c-a3fc3c916d61
select
BLOCK_TIMESTAMP::DATE as date,
'Stake' as status,
COUNT(DISTINCT TX_ID) as tx_cnt,
count(DISTINCT SIGNERS[0]) as user_cnt,
sum((PRE_BALANCES[0]/1e9) - (POST_BALANCES[0]/1e9)) as stake_amt,
avg((PRE_BALANCES[0]/1e9) - (POST_BALANCES[0]/1e9)) as avg_stake_amt
from solana.core.fact_staking_lp_actions
where EVENT_TYPE in ('delegate')
and SUCCEEDED = 'TRUE'
and block_timestamp::date >= CURRENT_DATE - 30
and PROGRAM_ID = 'Stake11111111111111111111111111111111111111'
GROUP BY 1
UNION
select
BLOCK_TIMESTAMP::DATE as date,
'Unstake' as status,
COUNT(DISTINCT TX_ID) as tx_cnt,
count(DISTINCT SIGNERS[0]) as user_cnt,
sum((POST_BALANCES[0]/1e9) - (PRE_BALANCES[0]/1e9)) as unstake_amt,
avg((POST_BALANCES[0]/1e9) - (PRE_BALANCES[0]/1e9)) as avg_unstake_amt
from solana.core.fact_staking_lp_actions
where EVENT_TYPE in ('withdraw')
and SUCCEEDED = 'TRUE'
and block_timestamp::date >= CURRENT_DATE - 30
and PROGRAM_ID = 'Stake11111111111111111111111111111111111111'
GROUP BY 1
Run a query to Download Data