Multipartite(Auto-updating) Top 9/10ths of RUNE depths
Updated 2023-09-27Copy Reference Fork
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
dim_convert AS (
SELECT dim_block_id, block_id
FROM thorchain.core.dim_block
),
depths AS
(
SELECT block_timestamp, block_id, pool_name,
rune_e8, asset_e8, synth_e8,
0 AS LPunitchange
FROM (thorchain.core.fact_block_pool_depths AS table1
INNER JOIN dim_convert ON table1.dim_block_id = dim_convert.dim_block_id)
WHERE (pool_name NOT LIKE '%/%')
AND (pool_name NOT LIKE 'THOR.%')
AND (rune_e8 > 0)
AND (asset_e8 > 0)
QUALIFY (block_id = MAX(block_id) OVER(PARTITION BY pool_name, DATE(block_timestamp)))
),
current_rune_depths AS (
SELECT block_id, pool_name, rune_e8
FROM depths
QUALIFY block_id = MAX(block_id) OVER(PARTITION BY pool_name)
),
top_portion_of_rune_depths AS (
SELECT pool_name, rune_e8,
ROUND(100 * rune_e8 / SUM(rune_e8) OVER(), 2) AS rune_depth_percentage,
ROUND(100 * SUM(rune_e8) OVER(ORDER BY rune_e8 DESC) / SUM(rune_e8) OVER(), 2) AS rune_depth_percentage_summing_downwards
FROM current_rune_depths
QUALIFY NOT((SUM(rune_e8) OVER() * 1/10) > SUM(rune_e8) OVER(ORDER BY rune_e8 ASC))
)
SELECT SPLIT(pool_name, '-')[0] AS pool,
ROUND(POWER(10,-8) * rune_e8, 2) AS rune_depth_ceiling,
rune_depth_percentage,
rune_depth_percentage_summing_downwards
Run a query to Download Data