BTC_depths AS

SELECT DISTINCT DATE(block_timestamp) AS date, block_timestamp,

asset_e8 AS btc_e8,

rune_e8 AS btc_rune_e8

FROM thorchain.core.fact_block_pool_depths

WHERE pool_name = 'BTC.BTC'

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

),

BUSD_depths AS

SELECT DISTINCT DATE(block_timestamp) AS date, block_timestamp,

asset_e8 AS busd_e8,

rune_e8 AS busd_rune_e8

FROM thorchain.core.fact_block_pool_depths

WHERE pool_name = 'BNB.BUSD-BD1'

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

SELECT BTC_depths.date, btc_e8, btc_rune_e8, busd_e8, busd_rune_e8,

(busd_e8 * btc_rune_e8) / NULLIF(busd_rune_e8 * btc_e8, 0) AS busd_per_btc,

btc_e8 / NULLIF(btc_rune_e8, 0) AS btc_per_rune,

btc_rune_e8 / NULLIF(btc_e8, 0) AS rune_per_btc,

busd_e8 / NULLIF(busd_rune_e8, 0) AS busd_per_rune

FROM (BTC_depths INNER JOIN BUSD_depths ON BTC_depths.date = BUSD_depths.date)

ORDER BY date DESC