0xHaM-dstaking/unstaking amount weekly copy
Updated 2024-08-20
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
›
⌄
WITH staking_unstaking AS (
SELECT
DATE_TRUNC('week', BLOCK_TIMESTAMP) AS date,
ACTION,
SUM(AMOUNT) AS total_amount
FROM
near.gov.fact_staking_actions
WHERE
ADDRESS LIKE 'meteor%'
AND ACTION IN ('staking', 'unstaking')
GROUP BY 1,2
)
SELECT
date,
SUM(CASE WHEN ACTION = 'staking' THEN total_amount ELSE 0 END) AS staking,
SUM(CASE WHEN ACTION = 'unstaking' THEN -total_amount ELSE 0 END) AS unstaking,
SUM(CASE WHEN ACTION = 'staking' THEN total_amount ELSE 0 END) +
SUM(CASE WHEN ACTION = 'unstaking' THEN -total_amount ELSE 0 END) AS net_amount,
SUM(staking) OVER (ORDER BY date) AS cumulative_staking,
SUM(unstaking) OVER (ORDER BY date) AS cumulative_unstaking,
SUM(net_amount) OVER (ORDER BY date) AS cumulati_net_tvl
FROM
staking_unstaking
GROUP BY
date
QueryRunArchived: QueryRun has been archived