eth_depths AS

SELECT DISTINCT DATE(block_timestamp) AS date, block_timestamp,

asset_e8 AS eth_e8,

rune_e8 AS eth_rune_e8

FROM thorchain.core.fact_block_pool_depths

WHERE pool_name = 'ETH.ETH'

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 eth_depths.date, eth_e8, eth_rune_e8, busd_e8, busd_rune_e8,

(busd_e8 * eth_rune_e8) / NULLIF(busd_rune_e8 * eth_e8, 0) AS busd_per_eth,

eth_e8 / NULLIF(eth_rune_e8, 0) AS eth_per_rune,

eth_rune_e8 / NULLIF(eth_e8, 0) AS rune_per_eth

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

ORDER BY date DESC