depths AS

SELECT DISTINCT DATE(block_timestamp) AS date, block_timestamp, pool_name,

rune_e8, asset_e8, synth_e8

FROM thorchain.core.fact_block_pool_depths

WHERE rune_e8 <> 0

QUALIFY (block_timestamp = MAX(block_timestamp) OVER(PARTITION BY pool_name, date))

),

current_rune_depths AS (

SELECT date, pool_name, rune_e8

FROM depths

QUALIFY date = MAX(date) 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))

),

selected_depths AS (

SELECT date, SPLIT(pool_name, '-')[0] AS pool,

rune_e8, asset_e8, synth_e8,

POWER(10,-8) * (asset_e8 - synth_e8) AS asset_equity,

POWER(10,-8) * (asset_e8 - synth_e8) * rune_e8 / NULLIF(asset_e8,0) AS equivalent_rune_equity,

--And, for log scales:

IFF(asset_equity < 0, -1 * asset_equity, 0) AS asset_deficit,

IFF(equivalent_rune_equity < 0, -1 * equivalent_rune_equity, 0) AS equivalent_rune_deficit

FROM depths

WHERE pool_name IN (SELECT pool_name FROM top_portion_of_rune_depths)

SELECT *

FROM selected_depths