dim_convert AS (

SELECT dim_block_id, block_id

FROM thorchain.core.dim_block

),

depths AS

SELECT DATE(reftable.block_timestamp) AS date, block_id, pool_name,

rune_e8, asset_e8, synth_e8,

0 AS LPunitchange

FROM (thorchain.defi.fact_block_pool_depths AS reftable INNER JOIN dim_convert ON reftable.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) AND (reftable.block_timestamp IS NOT NULL)

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

),

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 (

SELECT *

FROM depths