dim_convert AS (

SELECT dim_block_id, block_id

FROM thorchain.core.dim_block

),

saverdepths AS (

SELECT DATE(block_timestamp) AS date,

block_id AS saver_block_id,

pool_name,

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

POWER(10,-8) * asset_e8 AS savers_depth

FROM (thorchain.defi.fact_block_pool_depths INNER JOIN dim_convert ON fact_block_pool_depths.dim_block_id = dim_convert.dim_block_id)

WHERE (pool_name LIKE 'BNB/BUSD%')

--For an extra check that 'synth_amount' refers to synths.

AND (date >= '2022-11-12')

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

--AND (date <> (SELECT DATE(MAX(block_timestamp)) FROM thorchain.defi.fact_rewards_events)) --Not complete yet, so leave it out.

),

pools AS (

SELECT DISTINCT REPLACE(pool_name, '/', '.') AS pool_name

FROM saverdepths

),

pooldepths AS (

SELECT DATE(block_timestamp) AS date,

block_id AS pool_block_id,

pool_name,

POWER(10,-8) * (2 * asset_e8) AS asset_terms_liquidity,

POWER(10,-8) * asset_e8 AS asset_depth,

POWER(10,-8) * synth_e8 AS synth_supply,

rune_e8 / 1e8 AS rune_depth,

asset_e8 / rune_e8 AS asset_per_rune

FROM (thorchain.defi.fact_block_pool_depths AS table1 INNER JOIN dim_convert ON table1.dim_block_id = dim_convert.dim_block_id)

WHERE (pool_name in (SELECT pool_name FROM pools))