mamad-5XN3k32023-04-21 03:38 PM
Updated 2023-04-21
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
›
⌄
WITH staking as (
select
date_trunc('week',block_timestamp) as date,
delegator,
sum(amount) as volume,
sum(volume) over (partition by delegator order by date) as cum_volume,
avg(amount) as avg_volume
from flow.core.ez_staking_actions where action in ('DelegatorTokensCommitted','TokensCommitted')
group by 1,2
order by 1 asc),
unstaking as (
select
date_trunc('week',block_timestamp) as date,
delegator,
sum(amount) as volume,
sum(volume) over (partition by delegator order by date) as cum_volume,
avg(amount) as avg_volume
from flow.core.ez_staking_actions where action in ('UnstakedTokensWithdrawn','DelegatorUnstakedTokensWithdrawn')
group by 1,2
order by 1 asc),
final as (
select
x.date as dates,
x.delegator as delegator,
x.cum_volume as total_staked_volume,
y.cum_volume*(-1) as total_unstaked_volume,
total_staked_volume+total_unstaked_volume as total_net_staked_volume,
rank () over (partition by dates order by total_net_staked_volume desc ) as rank
from staking x join unstaking y on x.date=y.date and x.delegator=y.delegator
order by 1 asc
),
flow as (
select
Run a query to Download Data