WITH pricerefs AS (

SELECT DISTINCT DATE(block_timestamp) AS date,

rune_e8 AS rune_e8_ref,

asset_e8 AS asset_e8_ref

FROM thorchain.defi.fact_block_pool_depths

WHERE pool_name = 'ETH.USDT-0XDAC17F958D2EE523A2206206994597C13D831EC7'

--At present BNB.BUSD is hard-coded as the reference pool,

--but other approaches are to check the deepest USD stablecoin pool

--or to take a median of the stablecoin pools.

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

),

pools_rune AS (

SELECT DISTINCT DATE(block_timestamp) AS date,

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

rune_e8 AS pooled_rune_e8

FROM thorchain.defi.fact_block_pool_depths

WHERE pool_name NOT LIKE 'THOR.%' --Do not include Derived Asset pools.

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

SELECT pools_rune.date, pool,

asset_e8_ref / rune_e8_ref AS USD_price_of_RUNE,

POWER(10, -8) * pooled_rune_e8 AS pooled_RUNE,

pooled_rune * USD_price_of_RUNE AS USD_nonRUNE_value_locked

FROM (pools_rune INNER JOIN pricerefs ON pools_rune.date = pricerefs.date)

HAVING USD_nonRUNE_value_locked > 10

ORDER BY date DESC