scottincrypto[43] Angel Delegations
Updated 2021-08-19
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
›
⌄
⌄
with events_by_date as (
select
date_trunc('day', block_timestamp) as date,
delegator_address,
left(delegator_address, 10) as delegator_short,
action,
sum(event_amount) as amount,
avg(price_usd) as luna_price
from terra.staking
where validator_address = 'terravaloper1hqyygjq0vdqk0xdpkffgamzr4f7tqqg5u9mg66' and currency = 'LUNA'
group by 1,2,3,4
order by date
),
piv as (
select
*
from events_by_date
pivot(sum(amount) for action in ('delegate', 'redelegate', 'undelegate'))
as p (date, delegator_address, delegator_short, luna_price, delegate, redelegate, undelegate)
)
select
*,
sum(delegate) over (partition by delegator_address order by date rows between unbounded preceding and current row) as cumu_delegate,
sum(redelegate) over (partition by delegator_address order by date rows between unbounded preceding and current row) as cumu_redelegate,
sum(undelegate) over (partition by delegator_address order by date rows between unbounded preceding and current row) as cumu_undelegate
from piv
/*
case when action = 'delegate' then sum(event_amount) over (partition by delegator_address, action order by block_timestamp rows between unbounded preceding and current row) end as cumu_delegate,
case when action = 'redelegate' then sum(event_amount) over (partition by delegator_address, action order by block_timestamp rows between unbounded preceding and current row) end as cumu_redelegate,
case when action = 'undelegate' then sum(event_amount) over (partition by delegator_address, action order by block_timestamp rows between unbounded preceding and current row) end as cumu_undelegate
*/
Run a query to Download Data