mz0111Flow de 8
Updated 2023-04-22
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
›
⌄
--credit to https://flipsidecrypto.xyz/adriaparcerisas/q/flo-ws-path-to-decentralization-3--mCYet
WITH
staking as (
SELECT
trunc(block_timestamp,'month') as date,
node_id as validator,
count(distinct tx_id) as transactions,
sum(transactions) over (partition by validator order by date) as cum_transactions,
count(distinct delegator) as delegators,
sum(delegators) over (partition by validator order by date) as cum_delegators,
sum(amount) as volume,
sum(volume) over (partition by validator order by date) as cum_volume,
avg(amount) as avg_volume,
median(amount) as median_volume,
avg(volume) over (partition by validator order by date rows between 6 preceding and current row) as avg_7d_ma_volume
from flow.core.ez_staking_actions where action in ('DelegatorTokensCommitted','TokensCommitted')
group by 1,2 order by 1 asc
),
unstaking as (
SELECT
trunc(block_timestamp,'month') as date,
node_id as validator,
count(distinct tx_id) as transactions,
sum(transactions) over (partition by validator order by date) as cum_transactions,
count(distinct delegator) as delegators,
sum(delegators) over (partition by validator order by date) as cum_delegators,
sum(amount) as volume,
sum(volume) over (partition by validator order by date) as cum_volume,
avg(amount) as avg_volume,
median(amount) as median_volume,
avg(volume) over (partition by validator order by date rows between 6 preceding and current row) as avg_7d_ma_volume
from flow.core.ez_staking_actions where action in ('UnstakedTokensWithdrawn','DelegatorUnstakedTokensWithdrawn')
group by 1,2 order by 1 asc
),
final as (
SELECT
Run a query to Download Data