Multipartite(Auto-updating) Top 9/10ths of RUNE depths
    Updated 2023-09-27
    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