messariflow stats: flow staked (over time) weekly copy
Updated 2025-02-24
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 adriaparcerisas / flow stats: flow staked (over time) weekly @ https://flipsidecrypto.xyz/adriaparcerisas/q/7TxZIT9U8DZ1/flow-stats-flow-staked-over-time-weekly
WITH
staking as (
SELECT
trunc(block_timestamp,'day') as date,
--case when action in ('DelegatorTokensCommitted','TokensCommitted') then 'Staking',
--when action in ('UnstakedTokensWithdrawn','DelegatorUnstakedTokensWithdrawn') then 'Unstaking'
-- end as actions,
count(distinct tx_id) as transactions,
sum(transactions) over (order by date) as cum_transactions,
count(distinct delegator) as delegators,
sum(delegators) over (order by date) as cum_delegators,
sum(amount) as volume,
sum(volume) over (order by date) as cum_volume,
avg(amount) as avg_volume,
median(amount) as median_volume,
avg(volume) over (order by date rows between 6 preceding and current row) as avg_7d_ma_volume
from flow.gov.ez_staking_actions where action in ('DelegatorTokensCommitted','TokensCommitted')
group by 1 order by 1 asc
),
unstaking as (
SELECT
trunc(block_timestamp,'day') as date,
--case when action in ('DelegatorTokensCommitted','TokensCommitted') then 'Staking',
--when action in ('UnstakedTokensWithdrawn','DelegatorUnstakedTokensWithdrawn') then 'Unstaking'
-- end as actions,
count(distinct tx_id) as transactions,
sum(transactions) over (order by date) as cum_transactions,
count(distinct delegator) as delegators,
sum(delegators) over (order by date) as cum_delegators,
sum(amount) as volume,
sum(volume) over (order by date) as cum_volume,
avg(amount) as avg_volume,
median(amount) as median_volume,
QueryRunArchived: QueryRun has been archived