winnie-fs(Auto-updating) 9/10ths pooled-RUNE normalised PUVI chart copy
Updated 2023-04-03
999
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
›
⌄
-- forked from 205b2f67-8461-4a26-b6ff-ac2877b1056a
--Note that the first synth transaction was in block 4610718 (2022-03-09).
-- Check unit amounts with this URL:
-- https://thornode.ninerealms.com/thorchain/pool/[pool_name]?height=[block_id]
WITH
dim_convert AS (
SELECT block_timestamp, block_id
FROM thorchain.core.dim_block
),
depths AS
(
SELECT fact_block_pool_depths.block_timestamp, block_id, pool_name,
rune_e8, asset_e8, synth_e8,
0 AS LPunitchange
FROM (thorchain.core.fact_block_pool_depths INNER JOIN dim_convert ON fact_block_pool_depths.block_timestamp = dim_convert.block_timestamp)
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(fact_block_pool_depths.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
FROM current_rune_depths
QUALIFY NOT((SUM(rune_e8) OVER() * 1/10) > SUM(rune_e8) OVER(ORDER BY rune_e8 ASC))
),
selected_pool_depths AS (
Run a query to Download Data