daily_rune_liqfees AS (

SELECT DATE(block_timestamp) AS date,

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

SUM(liq_fee_in_rune_e8) / 1e8 AS daily_rune_liquidity_fees

FROM thorchain.core.fact_swaps_events

WHERE block_timestamp IS NOT NULL

AND pool_name = 'BTC.BTC' --To make chart display easier

GROUP BY date, pool

),

closing_rune_depth AS (

SELECT DATE(block_timestamp) AS date,

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

rune_e8 / 1e8 AS rune_depth

FROM (

SELECT DISTINCT block_timestamp, pool_name, rune_e8

FROM thorchain.core.fact_block_pool_depths

WHERE block_timestamp IS NOT NULL

and rune_e8 > 0

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

)

SELECT daily_rune_liqfees.date, daily_rune_liqfees.pool,

daily_rune_liquidity_fees, rune_depth,

daily_rune_liquidity_fees / rune_depth AS fees_per_depth,

rune_depth / daily_rune_liquidity_fees AS depth_per_fees

FROM (daily_rune_liqfees INNER JOIN closing_rune_depth

ON daily_rune_liqfees.date = closing_rune_depth.date

AND daily_rune_liqfees.pool = closing_rune_depth.pool)

ORDER BY date DESC, rune_depth DESC