SELECT DATE(reftable.block_timestamp) AS date,

block_id,

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

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

POWER(10,-8) * rune_e8 AS rune_depth

FROM (thorchain.core.fact_block_pool_depths AS reftable INNER JOIN thorchain.core.dim_block

ON reftable.dim_block_id = dim_block.dim_block_id)

WHERE (pool LIKE '%.%') AND (pool NOT LIKE 'THOR.%') AND (asset_e8 > 0) AND (rune_e8 > 0) AND (block_id > 0)

AND (pool LIKE '%USD%') -- USD stablecoin pools.

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

ORDER BY date DESC, asset_depth DESC