Multipartite2023-08-16 .7pm8 Pool Module RUNE movements in 2023-08
Updated 2023-08-16
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
lockedaddresses AS
(
SELECT
'thor1g98cy3n9mmjrpn0sxmn63lztelera37n8n67c0' AS pool_module
),
--Treasury addresses are available to go into LPs at will, so don't count as 'locked' in the same sense(s).
balance_changes AS
(
SELECT block_timestamp,
(CASE WHEN to_address = lockedaddresses.pool_module THEN from_address ELSE to_address END) AS address,
((CASE WHEN to_address = lockedaddresses.pool_module THEN amount_e8 ELSE 0 END)
- (CASE WHEN from_address = lockedaddresses.pool_module THEN amount_e8 ELSE 0 END))
AS pool_e8
FROM thorchain.core.fact_transfer_events, lockedaddresses
WHERE (asset = 'THOR.RUNE')
AND (DATE(block_timestamp) BETWEEN '2023-08-01' AND '2023-08-31')
AND (lockedaddresses.pool_module in (to_address, from_address))
),
cumulatives AS (
SELECT DATE_TRUNC('minute', balance_changes.block_timestamp) AS time, block_id,
pool_e8 / 1e8 AS pool_rune_balance_change,
SUM(pool_e8) OVER(ORDER BY block_id ASC) / 1e8 AS pool_relative_rune_balance
FROM balance_changes INNER JOIN thorchain.core.dim_block
ON balance_changes.block_timestamp = dim_block.block_timestamp
WHERE address = 'thor1s5dnekmjk79jgdh4vmk5yvawccewk0p3v57pm8'
--QUALIFY block_id = MAX(block_id) OVER(PARTITION BY time)
QUALIFY pool_rune_balance_change = MIN(pool_rune_balance_change) OVER(PARTITION BY time)
)
SELECT *
FROM cumulatives
ORDER BY pool_rune_balance_change ASC
LIMIT 100
Run a query to Download Data