Josh956cumulative both This is good
Updated 2023-01-31
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
›
⌄
with a as (select date_trunc('DAY', block_timestamp) as day, 'Stake' as platform,
sum(stake_amount/1e24) as "Stake amount in USD",
avg(stake_amount/1e24) as "Average stake amount in USD",
median(stake_amount/1e24) as "Median stake amount in USD",
max(stake_amount/1e24) as "Max stake amount in USD",
sum("Stake amount in USD") over (order by day) AS "Cumulative unstaked amount in USD",
count(distinct tx_hash) as "Stake Count",
count(distinct tx_signer) as "Unique Stakers",
sum("Unique Stakers") over (order by day) as "Cumulative staked unique stakers"
from near.core.dim_staking_actions
where action='Stake'
and day>'2023-01-01'
group by 1,2),
b as
(select date_trunc('day', block_timestamp) as day, 'Unstake' as platform,
sum(stake_amount/1e24) as "Unstake amount in USD",
avg(stake_amount/1e24) as "Average unstake amount in USD",
median(stake_amount/1e24) as "Median unstake amount in USD",
max(stake_amount/1e24) as "Max unstake amount in USD",
sum("Unstake amount in USD") over (order by day) as "Cumulative unstaked amount in USD",
count(distinct tx_hash) as "Unstake Count",
count(distinct tx_signer) as "Unique Unstakers",
sum("Unique Unstakers") over (order by day) as "Cumulative unstaked unique unstakers"
from near.core.dim_staking_actions
where action like '%Unstake%'
and day>'2023-01-01'
group by 1,2)
select * from a
union all
select * from b